SQL – 存在的非现有行

huangapple go评论64阅读模式
英文:

SQL - Not existing rows as existing

问题

I have 3 tables. One with a list of users, one with items and one with relation between the users and the items. User can have an item (stat = 0/1) or can have a duplicate (dup = 0/1). If you click on the item's or the duplicate's button, it runs a query that adds or updates the row.

It looks like this:

user item_id stat dup
17 1 1 1
5 2 1 0
8 1 0 1
9 4 1 0

I compare these data who have an item what the other does not have and vica versa with this query:

SELECT t2.user_id,
GROUP_CONCAT(CASE WHEN t1.dup=1 AND t2.stat=0 THEN item_id END) My Item List,
GROUP_CONCAT(CASE WHEN t2.dup=1 AND t1.stat=0 THEN item_id END) Item List
FROM table3 t1
LEFT JOIN table3 t2 USING (item_id)
WHERE t1.user_id = @current_user
AND t2.user_id <> @current_user
GROUP BY t2.user_id

My problem is I can only compare the rows if the user already clicked the buttons at least once, so the row generated, but I need the stat=0 and dup=0 rows as well even if they are not existing.

How can I integrate this to the query above?

My tables:

Table1

CREATE TABLE table1(
id NOT NULL AUTO_INCREMENT,
user_name varchar(255),
);

Table 2

CREATE TABLE table2(
id NOT NULL AUTO_INCREMENT,
item_no varchar(255),
item_name varchar(255),
item_group varchar(255)
);

Table 3

CREATE TABLE table3 (
id int NOT NULL AUTO_INCREMENT,
user_id int NOT NULL,
item_id int NOT NULL,
stat tinyint NOT NULL,
dup tinyint NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (user_id) REFERENCES table1(id),
FOREIGN KEY (item_id) REFERENCES table2(id)
);

Thank you!

英文:

I have 3 tables. One with a list of users, one with items and one with relation between the users and the items. User can have an item (stat = 0/1) or can have a duplicate (dup = 0/1). If you click on the item's or the duplicate's button, it runs a query that adds or updates the row.

It looks like this:

| user | item_id | stat | dup |
| ---- | ------- | ---- | --- |
| 17   |   1     |  1   |  1  |
| 5    |   2     |  1   |  0  |
| 8    |   1     |  0   |  1  |
| 9    |   4     |  1   |  0  |

I compare these data who have an item what the other does not have and vica versa with this query:

SELECT t2.user_id,
       GROUP_CONCAT(CASE WHEN t1.dup=1 AND t2.stat=0 THEN item_id END) `My Item List`,
       GROUP_CONCAT(CASE WHEN t2.dup=1 AND t1.stat=0 THEN item_id END) `Item List`
FROM table3 t1
LEFT JOIN table3 t2 USING (item_id)
WHERE t1.user_id = @current_user
  AND t2.user_id &lt;&gt; @current_user
GROUP BY t2.user_id

My problem is I can only compare the rows if the user already clicked the buttons at least once, so the row generated, but I need the stat=0 and dup=0 rows as well even if they are not existing.

How can I integrate this to the query above?

My tables:

Table1

CREATE TABLE table1(
    id NOT NULL AUTO_INCREMENT,
    user_name varchar(255),
);

Table 2

CREATE TABLE table2(
    id NOT NULL AUTO_INCREMENT,
    item_no varchar(255),
    item_name varchar(255),
    item_group varchar(255)
);

Table 3

CREATE TABLE table3 (
  id int NOT NULL AUTO_INCREMENT,
  user_id int NOT NULL,
  item_id int NOT NULL,
  stat tinyint NOT NULL,
  dup tinyint NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (user_id) REFERENCES table1(id),
  FOREIGN KEY (item_id) REFERENCES table2(id)
);

Thank you!

答案1

得分: 2

以下是代码部分的翻译:

首先,我强烈建议您将您的表格名称更改为更有意义的名称,以免混淆。

-- 我们将把所有项目 ID 放入一个变量中
DECLARE @All_Item_ids VARCHAR(MAX)
SET @All_Item_ids = SELECT STUFF((SELECT ', ' + t2.id
FROM table2 t2
FOR XML PATH('')), 1, 1, '')

-- 我们将把属于用户的所有项目 ID 放入一个变量中
DECLARE @User_Item_ids VARCHAR(MAX)
SET @User_Item_ids = SELECT STUFF((SELECT ', ' + t2.id
FROM table2 t2
WHERE t2.id IN (SELECT item_id FROM table3 WHERE user_id = @current_user)
FOR XML PATH('')), 1, 1, '')

-- 我们显示结果
SELECT @current_user, @User_Item_ids '我的项目列表', @All_Item_ids '项目列表'

英文:

First of all I would highly advise you to change the names of your Tables to something more meaningful so you (and we) don't get confused.

--We are going to group all of the Item Ids in one variable
DECLARE @All_Item_ids VARCHAR(MAX)
SET @All_Item_ids =SELECT STUFF((SELECT &#39;, &#39; + t2.id 
          FROM table2 t2
          FOR XML PATH(&#39;&#39;)), 1, 1, &#39;&#39;) 

--We are going to group all of the Item Ids that belong to a user in one variable
DECLARE @User_Item_ids VARCHAR(MAX)
SET @User_Item_ids =SELECT STUFF((SELECT &#39;, &#39; + t2.id 
          FROM table2 t2 
          WHERE t2.id IN (SELECT item_id FROM table3 WHERE user_id = @current_user)
          FOR XML PATH(&#39;&#39;)), 1, 1, &#39;&#39;)

--We are showing the result
SELECT @current_user,@User_Item_ids &#39;My Item List&#39; ,@All_Item_ids &#39;Item List&#39;

huangapple
  • 本文由 发表于 2023年4月6日 20:06:07
  • 转载请务必保留本文链接:https://go.coder-hub.com/75949338.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定