我正在尝试从联接的select查询结果中筛选出具有相同'id_user‘列值但排序优先级较低的行。列'sort1‘的优先级最高,而'sort4’的priority.The名最低,该列设置的是优先级,而不是其内的值。
表用户:
CREATE TABLE `users` (
`user_id` int(11) NOT NULL,
`name` varchar(30) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
INSERT INTO `users` (`user_id`, `name`) VALUES
(1, 'Some Name'),
(2, 'Some Other Name');
ALTER TABLE `users`
ADD PRIMARY KEY (`user_id`);表项:
CREATE TABLE `items` (
`id_user` int(11) NOT NULL,
`sort1` int(11) DEFAULT NULL,
`sort2` int(11) DEFAULT NULL,
`sort3` int(11) DEFAULT NULL,
`sort4` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
INSERT INTO `items` (`id_user`, `sort1`, `sort2`, `sort3`, `sort4`) VALUES
(1, NULL, NULL, 39, NULL),
(2, NULL, NULL, NULL, 45),
(1, NULL, 34, NULL, NULL);我尝试了下面的查询,得到的结果比我需要的要多:
SELECT
users.name,
items.id_user,
items.sort1,
items.sort2,
items.sort3,
items.sort4
FROM
items
LEFT JOIN users ON items.id_user = users.user_id
WHERE
(
items.sort1 = '' OR items.sort1 IS NULL
) AND(
items.sort2 = 34 OR items.sort2 IS NULL
) AND(
items.sort3 = 39 OR items.sort3 IS NULL
) AND(
items.sort4 = 45 OR items.sort4 IS NULL
)实际结果:
| Name | id_user | sort1 | sort2 | sort3 | sort4 |
+-----------------+---------+-------+-------+-------+-------|
| Some Name | 1 | NULL | NULL | 39 | NULL |
| Some Other Name | 2 | NULL | NULL | NULL | 45 |
| Some Name | 1 | NULL | 34 | NULL | NULL |第一行是额外的结果,因为第三行具有更高的优先级(Sort2)。
预期结果:
| Name | id_user | sort1 | sort2 | sort3 | sort4 |
+-----------------+---------+-------+-------+-------+-------|
| Some Name | 1 | NULL | 34 | NULL | NULL |
| Some Other Name | 2 | NULL | NULL | NULL | 45 |我还尝试了GROUP BY id_user和ORDER BY priority列,但没有得到预期的结果
发布于 2020-05-27 03:02:52
我更改了您的items表,添加了id字段
CREATE TABLE `items` (
`id` int(11) NOT NULL,
`id_user` int(11) NOT NULL,
`sort1` int(11) DEFAULT NULL,
`sort2` int(11) DEFAULT NULL,
`sort3` int(11) DEFAULT NULL,
`sort4` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
ALTER TABLE `items` ADD PRIMARY KEY (`id`);
INSERT INTO `items` (`id`, `id_user`, `sort1`, `sort2`, `sort3`, `sort4`) VALUES
(1, 1, NULL, NULL, 39, NULL),
(2, 2, NULL, NULL, NULL, 45),
(3, 1, NULL, 34, NULL, NULL);所以你可以尝试这样的请求,我把FROM子句从items改为users
SELECT
users.name,
items.id,
items.id_user,
items.sort1,
items.sort2,
items.sort3,
items.sort4
FROM
users
LEFT JOIN
items ON items.id = (
SELECT i.id FROM items AS i WHERE i.id_user = users.user_id ORDER BY -i.sort1 DESC, -i.sort2 DESC, -i.sort3 DESC, -i.sort4 DESC LIMIT 1
)
ORDER BY -items.sort1 DESC, -items.sort2 DESC, -items.sort3 DESC, -items.sort4 DESC结果
| name | id | id_user | sort1 | sort2 | sort3 | sort4 |
|-----------------|----|---------|--------|--------|--------|--------|
| Some Name | 3 | 1 | (null) | 34 | (null) | (null) |
| Some Other Name | 2 | 2 | (null) | (null) | (null) | 45 |https://stackoverflow.com/questions/62027791
复制相似问题