MYSQL:选择字段值的总和，同时还选择唯一值? [英] MYSQL: SELECT sum of field values while also SELECTing unique values?

问题描述

我想计算每件商品的购买数量，同时还要根据用户查看内容的方式，显示用户是否购买了内容.因为物品和购买的数量可能会变大，所以我不愿意投入更多的JOINs来完成此任务，因为这似乎没有效果.

I'd like to count the number of purchases of each item while also, depending on who's viewing the content, show whether the user has purchased the content. Because the number of items and purchases could become large I'm reluctant to throw in more JOINs to accomplish this because that would seem not performant.

基本上，我想在下面的查询中的某个地方有一个did_i_buy字段，而不添加另一个JOIN.这可能吗?假设user_name=tom:

Basically, I'd like to have a did_i_buy field somewhere in the following query without adding another JOIN. Is this possible? Let's say for user_name=tom:

SELECT Items.item_id, item_name, COUNT(purchase_status='bought') as number_bought 
FROM Purchases
JOIN Items ON Purchases.item_id=Items.item_id
GROUP BY Items.item_id

这是我的数据库结构:

Table Items
item_id item_name
1           item_1
2           item_2
3           item_3

Table Purchases
item_id  purchase_status    user_name
1           bought          joe
2           bought          joe
1           bought          tom
1           bought          bill

tom

item_id item_name number_bought did_i_buy
1        item_1         3        yes
2        item_2         1        no

推荐答案

如果我理解正确，则did_i_buy列的意思是汤姆购买了".您可以这样做:

If I understand correctly, the did_i_buy column means "did Tom buy". You can do that like this:

SELECT
  Items.item_id,
  item_name,
  COUNT(CASE WHEN purchase_status='bought' THEN 1 END) as number_bought,
  MAX(CASE WHEN purchase_status='bought' AND user_name='Tom' THEN 'yes' ELSE 'no' END) AS did_i_buy
FROM Purchases
JOIN Items ON Purchases.item_id=Items.item_id
GROUP BY Items.item_id

或者(一个CASE语句，请参见下面的注释)

Alternatively (one CASE statement, see comments below)

SELECT
  Items.item_id,
  item_name,
  COUNT(purchase_status='bought') as number_bought,
  MAX(CASE WHEN user_name='Tom' THEN 'yes' ELSE 'no' END) AS did_i_buy
FROM Purchases
JOIN Items ON Purchases.item_id=Items.item_id
WHERE purchase_status='bought'
GROUP BY Items.item_id

还有一项调整::由于有WHERE子句，COUNT只会看到purchase_status='bought'所在的行，因此可以省略检查状态的表达式(唯一的变化是在第4行中):

And one more tweak: Because of the WHERE clause, the COUNT is only going to see rows where purchase_status='bought', so the expression checking the status can be left out (the only change from above is in line 4):

SELECT
  Items.item_id,
  item_name,
  COUNT(*) as number_bought,
  MAX(CASE WHEN user_name='Tom' THEN 'yes' ELSE 'no' END) AS did_i_buy
FROM Purchases
JOIN Items ON Purchases.item_id=Items.item_id
WHERE purchase_status='bought'
GROUP BY Items.item_id

这篇关于MYSQL:选择字段值的总和，同时还选择唯一值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！