mysql - 我的任务是获取两个选择按列合并的一个查询结果

Question

第一个查询:

SELECT COUNT(*)
FROM `test` t ,`user_test` ut,`user` u 
WHERE test_type_id = 1
AND t.created_on >= DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND t.created_on < CURDATE()
AND t.id = ut.test_id
AND u.id = ut.user_id
AND u.user_account_type_id != X 
AND u.usr NOT LIKE '%...' 
AND u.usr NOT LIKE '%...' 
AND u.usr NOT LIKE '%...'  
AND u.usr NOT LIKE '%...' 
AND u.usr NOT LIKE '%...' 
AND u.usr NOT LIKE '%...' 

第二个查询:

SELECT COALESCE(SUM(IF(tt.type = 0, 1, 0)), 0) aaa, COALESCE(SUM(IF(tt.type = 1, 1, 0)), 0) bbb 
FROM transaction_test tt ,USER u
WHERE tt.created_on >= DATE_SUB(CURDATE(), INTERVAL 10000 DAY) AND tt.created_on < CURDATE()
AND u.id = tt.user_id
AND u.user_account_type_id != X
AND u.usr NOT LIKE '%...' 
AND u.usr NOT LIKE '%...' 
AND u.usr NOT LIKE '%...'  
AND u.usr NOT LIKE '%...' 
AND u.usr NOT LIKE '%...' 
AND u.usr NOT LIKE '%...' 

必须从一个单一的选择中获得结果我试试这个

SELECT COALESCE(SUM(IF(tt.type = 0, 1, 0)), 0) Email, COALESCE(SUM(IF(tt.type = 1, 1, 0)), 0) SMS ,(
SELECT COUNT(*)
FROM `test` t ,`user_test` ut,`user` u 
WHERE test_type_id = 1
AND t.created_on >= DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND t.created_on < CURDATE()
AND t.id = ut.test_id
AND u.id = ut.user_id
AND u.user_account_type_id != X 
AND u.usr NOT LIKE '%...' 
AND u.usr NOT LIKE '%...' 
AND u.usr NOT LIKE '%...'  
AND u.usr NOT LIKE '%...' 
AND u.usr NOT LIKE '%...' 
AND u.usr NOT LIKE '%...' 
)
FROM transaction_test tt ,USER u
WHERE tt.created_on >= DATE_SUB(CURDATE(), INTERVAL 10000 DAY) AND tt.created_on < CURDATE()
AND u.id = tt.user_id
AND u.user_account_type_id != X
AND u.usr NOT LIKE '%...' 
AND u.usr NOT LIKE '%...' 
AND u.usr NOT LIKE '%...'  
AND u.usr NOT LIKE '%...' 
AND u.usr NOT LIKE '%...' 
AND u.usr NOT LIKE '%...' 

我对使用两次并不满意

AND u.user_account_type_id != X
AND u.usr NOT LIKE '%...' 
AND u.usr NOT LIKE '%...' 
AND u.usr NOT LIKE '%...'  
AND u.usr NOT LIKE '%...' 
AND u.usr NOT LIKE '%...' 
AND u.usr NOT LIKE '%...' 

这部分任何人都可以建议如何避免这种情况？提前致谢

Answer 1

试一试我已经加入了你的两个查询，因为在查询中你有相同的条件，我已经使用 INNER JOIN 来确保两个表都有数据，如果你想从中获取数据右表或左表相应地更改为 RIGHT JOIN 或 LEFT JOIN

SELECT COUNT(*) ,COALESCE(SUM(IF(tt.type = 0, 1, 0)), 0) aaa, COALESCE(SUM(IF(tt.type = 1, 1, 0)), 0) bbb 
FROM `test` t ,
INNER JOIN `user_test` ut ON (t.id = ut.test_id)
INNER JOIN `user` u  ON (u.id = ut.user_id)
INNER JOIN `transaction_test` tt  ON (u.id = tt.user_id)
WHERE test_type_id = 1
AND t.created_on >= DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND t.created_on < CURDATE()
AND u.user_account_type_id != X 
AND u.usr NOT LIKE '%...' AND u.usr NOT LIKE '%...' AND u.usr NOT LIKE '%...' 
AND u.usr NOT LIKE '%...' AND u.usr NOT LIKE '%...' AND u.usr NOT LIKE '%...' 

希望一切顺利