简单的sql语句

/**********查询渠道********************/
select DATE_FORMAT(tuc.create_time,'%Y-%m-%d'),count(tuc.user_id) from t_user_channel tuc inner join t_channel t on t.id=tuc.channel_id
where DATE_FORMAT(tuc.create_time,'%Y-%m-%d %H:%i:%S')>='2018-10-18 00:00:00'
and DATE_FORMAT(tuc.create_time,'%Y-%m-%d %H:%i:%S')<'2018-10-19 00:00:00'
group by DATE_FORMAT(tuc.create_time,'%Y-%m-%d');

select DATE_FORMAT(tuc.create_time, '%Y-%m-%d') ,t.channel_name,
count(tuc.user_id) from t_user_channel tuc
inner join t_channel t on t.id=tuc.channel_id
where DATE_FORMAT(tuc.create_time,'%Y-%m-%d %H:%i:%S')>='2018-10-18 00:00:00'
and DATE_FORMAT(tuc.create_time,'%Y-%m-%d %H:%i:%S')<'2018-10-19 00:00:00'
group by t.id, DATE_FORMAT(tuc.create_time,'%Y-%m-%d');
/*一级注册/
select * from t_user where id in (select invited_user_id from t_invite_share_info
where share_user_id=58718 and created_at >='2018-10-18 00:00:00' and created_at <'2018-10-19 00:00:00'
and level='invite_son')

/*二级注册/
select * from t_user where id in (select invited_user_id from t_invite_share_info
where share_user_id=58718 and created_at >='2018-10-18 00:00:00' and created_at <'2018-10-19 00:00:00'
and level='invite_grandson')

/一级开通钱包/
select * from t_user where id in (select invited_user_id from t_invite_share_info
where share_user_id=66324 and created_at >='2018-10-11 00:00:00' and created_at <'2018-10-12 00:00:00' and wallet_address is not null
and level='invite_son')
/二级开通钱包/
select * from t_user where id in (select invited_user_id from t_invite_share_info
where share_user_id=93078 and created_at >='2018-10-18 00:00:00' and created_at <'2018-10-19 00:00:00' and wallet_address is not null
and level='invite_grandson')

-- 排重处理count
select tb.id,tb.phone,tb.state,count(*) as count from t_blacklist_wangyi tb where tb.phone is not null group by tb.phone having count>1 ;

-- select tb.id,tb.asset_id,tb.asset,tb.type,count(*) as count from t_personal_asset_detail tb where tb.asset_id is not null and created_at >='2018-10-18 00:00:00'
-- group by tb.asset_id ORDER BY tb.created_at;

-- 查询t_personal_asset_detail表，时间是2018.10.18，然后将asset_id同种类别的 按时间排序 （type也就按时间显示出来了）
SELECT * FROM t_personal_asset_detail where created_at>='2018-10-18 00:00:00' and created_at<'2018-10-19 00:00:00' ORDER BY asset_id, created_at

SELECT * FROM t_personal_asset_detail ORDER BY asset_id, created_at

SELECT * FROM t_personal_asset_detail ORDER BY asset_id=(select top 1 asset_id and type =2 From t_personal_asset_detail), created_at

SELECT * FROM t_withdraw_cash_info ORDER BY user_id

SELECT SUM(asset_id=1805) FROM t_personal_asset_detail WHERE type=1

-- 为了查询cash表是否正常
SELECT id FROM t_cash_asset WHERE user_id=84356 /通过user_id获取到了asset_id/

SELECT * FROM t_cash_asset_detail WHERE asset_id IN (SELECT id FROM t_cash_asset WHERE user_id=84356)/通过asset_id获取到了该用户的详细列表/

SELECT value,type FROM t_cash_asset_detail WHERE asset_id IN (SELECT id FROM t_cash_asset WHERE user_id=84356)

/判断用户是否在黑名单/
SELECT * FROM t_blacklist WHERE phone=13653460057
-- 判断是否在交易黑名单
SELECT * FROM t_blacklist black WHERE black.phone IN (select phone from t_user ta where ta.id IN (select user_id from t_withdraw_cash_info WHERE created_at LIKE '2018-11-01%'))
-- 判断是否在登录账户黑名单
SELECT * FROM lsdk2.t_blacklist black WHERE black.phone IN (select phone from db_bpcm.t_user ta where ta.id IN (select user_id from db_bpcm.t_withdraw_cash_info WHERE created_at LIKE '2018-11-01%'))

-- //查看用户订单对应详情，两个表连接在一起
SELECT * FROM t_bc_order_head a
LEFT JOIN
t_bc_order_item b on b.order_no=a.order_no
WHERE from_uesr_id=85992
-- 墙 (select * from t_cash_asset ta where ta.user_id IN (select user_id from t_withdraw_cash_info))在提现表里面查出所有用户id然后在asset表里面取所有记录
select a.,tw.account_name,tw.account_no,tw.amount,tw.status,tw.created_at,b.reduce,c.plus,c.plus-b.reduce from
(select * from t_cash_asset ta where ta.user_id IN (select user_id from t_withdraw_cash_info WHERE created_at LIKE '2018-11-01%')) a
left join
(select td.asset_id assetb,sum(value) reduce
from t_cash_asset_detail td where td.type in (0,2,10) AND td.creat_time<='2018-11-02 00:00:00'
and td.asset_id in (select id from t_cash_asset ta where ta.user_id IN (select user_id from t_withdraw_cash_info WHERE created_at LIKE '2018-11-01%'))
group by td.asset_id) b/分类所有人单独需要减去的金额*/
on a.id = b.assetb
left join
(select td.asset_id assetc,sum(value) plus
from t_cash_asset_detail td where td.type in (1,3,6,7,8,9,11) AND td.creat_time<='2018-11-02 00:00:00'
and td.asset_id in (select id from t_cash_asset ta where ta.user_id IN (select user_id from t_withdraw_cash_info WHERE created_at LIKE '2018-11-01%'))
group by td.asset_id) c
on a.id = c.assetc
left join t_withdraw_cash_info tw
on a.user_id = tw.user_id AND created_at LIKE '2018-11-01%'

-- 查询提现表是否在黑名单
-- --查询积分余额是否正常
select c.asset_id,a.down,b.up from
(select td.asset_id from t_personal_asset_detail td group by td.asset_id ) c
left join
(select sum(td.asset) down,td.asset_id from t_personal_asset_detail td where td.type in (2,4,5)
and td.created_at<='2018-11-05 15:19:21'
group by td.asset_id) a
on c.asset_id = a.asset_id
left join
(select sum(td.asset) up,td.asset_id from t_personal_asset_detail td where td.type in (1,3,6,7,8,9)
and td.created_at<='2018-11-05 15:19:21'
group by td.asset_id) b
on c.asset_id = b.asset_id
where c.asset_id = 3426

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 204,189评论 6赞 478
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 85,577评论 2赞 381
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 150,857评论 0赞 337
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,703评论 1赞 276
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,705评论 5赞 366
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,620评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 37,995评论 3赞 396
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,656评论 0赞 258
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,898评论 1赞 298
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,639评论 2赞 321
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,720评论 1赞 330
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,395评论 4赞 319
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,982评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,953评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,195评论 1赞 260
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 44,907评论 2赞 349
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,472评论 2赞 342

推荐阅读更多精彩内容