大家好，大家好，
我在用posgresql编写复杂查询时遇到了一些困难。事实上，我在编写这个特定的查询周期时遇到问题，需要您的帮助。我们开始吧。
这是我的桌子
[products_tb] [client_tb] [sales_tb]
+--------------+ +-------------+ +-------------+--------------+-----+
| product_name | | client_name | | client_name | product_name | qty |
+--------------+ +-------------+ +-------------+--------------+-----+
| shoe | | john | | john | shoe | 20 |
+--------------+ +-------------+ +-------------+--------------+-----+
| belt | | bob | | john | belt | 9 |
+--------------+ +-------------+ +-------------+--------------+-----+
| kim | | bob | shoe | 2 |
+-------------+ +-------------+--------------+-----+
| bob | belt | 98 |
+-------------+--------------+-----+
| kim | shoe | 46 |
+-------------+--------------+-----+
| kim | belt | 3 |
+-------------+--------------+-----+
[query output] # this output will be displayed using php and html
+----------+-------+------+-----+-----+
| products | Total | john | bob | kim |
+----------+-------+------+-----+-----+
| shoe | 68 | 20 | 2 | 46 |
+----------+-------+------+-----+-----+
| belt | 110 | 9 | 98 | 3 |
+----------+-------+------+-----+-----+
我正在尝试编写一个查询，它将允许我生成表[查询输出]。任何帮助都将不胜感激。
非常感谢你。
最佳答案
不使用交叉选项卡的版本：
select p.product_name, sum(s.qty),
sum(CASE WHEN c.client_name='john' THEN s.qty END) as john,
sum(CASE WHEN c.client_name='bob' THEN s.qty END) as bob,
sum(CASE WHEN c.client_name='kim' THEN s.qty END) as kim
from products_tb AS p
JOIN sales_tb AS s ON p.product_name=s.product_name
JOIN clients_tb AS c ON c.client_name=s.client_name
GROUP BY p.product_name;
http://sqlfiddle.com/#!12/afc9a/10
关于postgresql - PostgreSQL中的复杂查询，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/15336937/