问题

以下是你提供的内容的翻译：

我有3个表。一个表包含用户列表，一个表包含物品，还有一个表用于用户和物品之间的关系。用户可以拥有一个物品（stat = 0/1），也可以拥有一个重复项（dup = 0/1）。

表1

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

表2

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

表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)
);

表3的示例数据：

我想创建一个查询，其结果如下（已登录的用户ID为17）：

在"My Item List"中，应该是WHERE userid=17 AND dup=1，同时userid!=17 AND stat=0。这意味着用户17有一个重复的物品，而其他用户没有这个物品。在"Item List"中，应该是WHERE userid !=17 AND dup=1，同时userid=17 AND stat=0。这意味着其他用户有一个或多个重复的物品，而用户17没有这些物品。

到目前为止，这是我得到的结果，但仍然缺少一些物品和信息：

SELECT 
  max(t1.username) AS 'Username',
  group_concat(DISTINCT CASE WHEN t3_2.user_id = 17 THEN t2.item_no END ORDER BY t2.id ASC separator ', ') AS 'MY Item List',
  group_concat(DISTINCT CASE WHEN t3_1.user_id != 17 THEN t2.item_no END ORDER BY t2.id ASC separator ', ') AS 'Item List'
FROM 
  table3 t3_1
INNER JOIN 
  table1 t1 
    ON t3_1.user_id = t1.id
INNER JOIN 
  table2 t2 
    ON t3_1.item_id = t2.id
LEFT JOIN 
  table3 t3_2 
    ON  t3_2.item_id = t3_1.item_id 
    AND t3_2.user_id = 17 
    AND t3_2.dup = 1
WHERE 
  t3_1.dup = 1 
  AND t3_1.item_id != 17 
  AND t3_1.stat = 0
GROUP BY 
  t1.id
ORDER BY 
  COUNT(DISTINCT t2.item_no) DESC;

感谢你的帮助。

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

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

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)
);

SELECT 
  max(t1.username) AS 'Username',
  group_concat(DISTINCT CASE WHEN t3_2.user_id = 17 THEN t2.item_no END ORDER BY t2.id ASC separator ', ') AS 'MY Item List',
  group_concat(DISTINCT CASE WHEN t3_1.user_id != 17 THEN t2.item_no END ORDER BY t2.id ASC separator ', ') AS 'Item List'
FROM 
  table3 t3_1
INNER JOIN 
  table1 t1 
    ON t3_1.user_id = t1.id
INNER JOIN 
  table2 t2 
    ON t3_1.item_id = t2.id
LEFT JOIN 
  table3 t3_2 
    ON  t3_2.item_id = t3_1.item_id 
    AND t3_2.user_id = 17 
    AND t3_2.dup = 1
WHERE 
  t3_1.dup = 1 
  AND t3_1.item_id != 17 
  AND t3_1.stat = 0
GROUP BY 
  t1.id
ORDER BY 
  COUNT(DISTINCT t2.item_no) DESC;

答案1

得分: 1

SELECT t2.user_id,
       GROUP_CONCAT(CASE WHEN t1.dup=1 AND t2.stat=0 THEN item_id END) `我的物品清单`,
       GROUP_CONCAT(CASE WHEN t2.dup=1 AND t1.stat=0 THEN item_id END) `物品清单`
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

fiddle

<details>
<summary>英文:</summary>

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

| user\_id | My Item List | Item List |
|--------:|:-------------|:----------|
| 5 | 1,2 | 3,4 |
| 8 | 2 | 4 |

[fiddle](https://dbfiddle.uk/pPeDGMCx)


</details>