简单的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