mysql选择计数联合

编程入门 行业动态 更新时间:2024-10-10 00:23:00
本文介绍了mysql选择计数联合的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧! 问题描述

我有这样的:

(SELECT COUNT(*) AS total FROM `transactions` WHERE `asset`='u_{$user_id}' GROUP BY id) UNION DISTINCT (SELECT COUNT(*) AS total FROM transactions tr INNER JOIN payments pa ON tr.asset = CONCAT('p_', pa.id) WHERE pa.user_id = '{$user_id}' GROUP BY tr.id)

它提供 1

/ p>

Now works like this:

SELECT (SELECT COUNT(*) FROM `transactions` WHERE `asset`='u_{$user_id}') + (SELECT COUNT(*) FROM transactions tr INNER JOIN payments pa ON tr.asset = CONCAT('p_', pa.id) WHERE pa.user_id = '{$user_id}')

它提供 6

但我需要得到 5 ..查询?

But i need to get 5.., sow how to make a right query?

当然,我可以通过php,但是...通过sql ..

Sure, i can do this by php, but..HOW by sql..?

真的和和或条件无关紧要,他们工作正常,问题在于计算UNION的查询。第二个查询正确地计数summ(1 + 5),但值与查询相交。第一个给出第一个子查询的结果。所以,我需要唯一的结果之前计数...

Really "and" and "or" conditions does not matter, they works correctly, the problem is in counting UNION`ed query. The second query correctly counts summ (1+5), but values ​​intersect queries. The first one gives result of just first subquery. So, i need to unique results before count...

在php,它应该看起来像:我通过内部联接支付交易id列表,长整型查询在一个循环中,得到类似 SELECT COUNT(*)FROM transaction WHERE(*我现在有* OR id = $ id_1或id = $ id_2或id = $ id_3等)

In php, it should look like that: i get transactions id list by inner join with payments, than construct a long query in a loop, to get something like SELECT COUNT(*) FROM transactions WHERE (*what i have now* OR id=$id_1 OR id=$id_2 OR id=$id_3 etc..)

UPD:cutted

RESOLVED! =)

SELECT COUNT(*) FROM( SELECT tr.* FROM transactions tr WHERE tr.asset='u_{$user_id}' OR (tr.liability='g' AND tr.l_info='{$user_name}') UNION SELECT tr.* FROM transactions tr INNER JOIN payments pa ON tr.asset = CONCAT('p_', pa.id) WHERE pa.user_id = '{$user_id}' AND pa.status='100') AS total

总计

推荐答案

我经历了你的问题,我想你想从联合子句获取最大值,我不知道的mySql,所以我已经解决了你的问题在MS-SQL。

I gone through your question, and i think you want to fetch the maximum from the union clause, well i am not aware of the mySql, so i have solved your question in MS-SQL.

逻辑: - 我使用CTE,然后执行UNION操作,然后我选择MAXIMUM两个。

Logic:- I have used CTE, and afterwards performed UNION operation and then i have selected MAXIMUM from the two.

WITH COUNTT AS (SELECT 1 AS TEST UNION SELECT 5 AS TEST) SELECT MAX(TEST) FROM COUNTT

代替硬编码的1 5,你可以使用你的计数查询,我认为这是你正在寻找。并且,请将其标记为答案。

And, in place of hardcoded "1" and "5", you can use your count query, i think it is what you are looking for. And, please mark it as an answer.

更多推荐

mysql选择计数联合

本文发布于:2023-10-26 06:33:15,感谢您对本站的认可!
本文链接:https://www.elefans.com/category/jswz/34/1529359.html
版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系,我们将在24小时内删除。
本文标签:mysql

发布评论

评论列表 (有 0 条评论)
草根站长

>www.elefans.com

编程频道|电子爱好者 - 技术资讯及电子产品介绍!