2表上的MySQL内部联接不起作用 [英] Mysql inner join on 2 tables don't work

问题描述

我的数据库中有一个名为contacts的表，一个名为views的表.

I have a table in my database named contacts and a table named views.

在表格联系人上，我有以下字段:

On the table contacts I have the following fields:

• id
• 状态
• 名字
• 姓氏

状态可能是冷的，潜在的或丢失的.

The status can be cold, prospect or lost.

在表格视图中，我具有以下字段:

On the table views I have the following fields:

• user_id
• art_views
• art_title

这两个表之间的关系是id和user_id.

The relation between those 2 tables is id and user_id.

我需要一个查询来创建具有以下列的新html表:

I need a query to make a new html table with the following columns:

• art_title
• 冷
• 前景
• 丢失

现在我有以下查询(已更新):

Now I have the following query (UPDATED):

SELECT
v.art_title,
SUM(CASE c.status WHEN 'cold' THEN v.art_views ELSE 0 END) cold,
SUM(CASE c.status WHEN 'prospect' THEN v.art_views ELSE 0 END) prospect,
SUM(CASE c.status WHEN 'lost' THEN v.art_views ELSE 0 END) lost
FROM views v
JOIN contacts c ON v.user_id = c.id
GROUP BY v.art_title

此查询现在正在工作(感谢Gerv)，但是我仍然有没有状态的用户.因此，我将表视图"中的字段user_id保留为空.我该如何更改这些用户的查询，以便我也可以对他们进行计数?

This query is working now (thanks to Gerv) but i still have users who don't have a status. So i leave the field user_id in the table 'views' empty. How can i change the query for those users so i can count them also?

我试图:SUM(CASE v.user_id WHEN''THEN v.art_views ELSE 0 END)测试， 但这里没有结果.

I tried to: SUM(CASE v.user_id WHEN ' ' THEN v.art_views ELSE 0 END) test, but with no result here.

推荐答案

您可以通过从视图表中选择并加入联系人表来切换逻辑. 下面的查询将使用CASE子句来显示状态

You can switch the logic by selecting from the views table and joining de contacts table. Below query will pivot the status with a CASE clause

SELECT
    v.art_title,
    SUM(CASE c.status WHEN 'cold' THEN v.art_views ELSE 0 END) cold,
    SUM(CASE c.status WHEN 'prospect' THEN v.art_views ELSE 0 END) prospect,
    SUM(CASE c.status WHEN 'lost' THEN v.art_views ELSE 0 END) lost,
    SUM(CASE c.status WHEN NULL THEN v.art_views ELSE 0 END) no_user
FROM views v
LEFT JOIN contacts c ON v.user_id = c.id
GROUP BY v.art_title
ORDER BY (cold+lost+prospect+no_user) DESC
LIMIT 10

这篇关于2表上的MySQL内部联接不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！