MySQL选择从并显示不带标记的结果 [英] MySQL Select From To And Show Results Without Marker

问题描述

我有桌子:

+----+-------------------+
| id | channel           |
+----+-------------------+
| m2 | AA-AA             |
| 1  | KR_A 1            |
| 2  | KR_A 2            |
| 11 | HR_A 3            |
| 12 | HR_B 1            |
| m4 | BB-BB             |
| m3 | CC-CC             |
| 17 | SR_B 1            |
| m5 | DD-DD             |
| m1 | EE-EE             |
+----+-------------------+

当我添加m2时，查询需要返回:

When i add m2 query needs to return:

1
2
11
12

所以我使用以下查询:

SET @showChannels:=0;
SELECT id, channel
FROM table
WHERE
(
   @showChannels:=( (id like 'm%' and id = 'm2' and @showChannels) or (id not like 'm%' ) )
)
AND id not like 'm%';

可以正确返回高于预期结果的结果.

That returns correctly above desired results.

当我添加m3进行查询时，问题是这样的:

Problem is this when i add m3 to query i need to get this:

17

但是我总是得到:

1
2
11
12

那是不正确的，因为我只需要获取17值...可以对它进行修改有帮助吗?我今天尝试了4个小时，无法达到预期的效果.

And that is not correct because i need to get only 17 value...could be any help to modify it to work? I try today for 4 hours and was unable to achieve the desired result.

推荐答案

只需最少的更改，您就可以向表中添加一个排序列，并将值更新为所需的值以反映正确的排序:

With minimal changes, you can add a sort column to your table, and update the values to the values you want that reflects the proper sorting:

ALTER TABLE table ADD COLUMN sort_seq INTEGER;

UPDATE table SET sort_seq = 1 WHERE id = 'm2';
UPDATE table SET sort_seq = 2 WHERE id = '1';
UPDATE table SET sort_seq = 3 WHERE id = '2';
UPDATE table SET sort_seq = 4 WHERE id = '11';
UPDATE table SET sort_seq = 5 WHERE id = '12';
UPDATE table SET sort_seq = 6 WHERE id = 'm4';
UPDATE table SET sort_seq = 7 WHERE id = 'm3';
UPDATE table SET sort_seq = 8 WHERE id = '17';
UPDATE table SET sort_seq = 9 WHERE id = 'm5';
UPDATE table SET sort_seq = 10 WHERE id = 'm1';

但是，您遇到的核心问题是您需要反向引用某种标记".这样做的更好方法是拥有一种对记录进行分组的方法.您可以通过为记录组添加查找表来做到这一点:

However, the core problem that you are having is that you need to back-reference to a "marker" of some kind. The better way to do that would have been to have a way to group your records. You can do that by adding a lookup table for groups for your records:

+----+-------------------+--------+--------+
| id | channel           |  grp   |  sort  |
+----+-------------------+--------+--------+
| m2 | AA-AA             |   m2   |    1   |
| 1  | KR_A 1            |   m2   |    2   |
| 2  | KR_A 2            |   m2   |    3   |
| 11 | HR_A 3            |   m2   |    4   |
| 12 | HR_B 1            |   m2   |    5   |
| m4 | BB-BB             |   m4   |    6   |
| m3 | CC-CC             |   m3   |    7   |
| 17 | SR_B 1            |   m3   |    8   |
| m5 | DD-DD             |   m5   |    9   |
| m1 | EE-EE             |   m1   |   10   |
+----+-------------------+--------+--------+

+----+--------------------+--------+
| id | group_desc         |  sort  |
+----+--------------------+--------+
| m1 | Description for m1 |   1    |
| m2 | Description for m2 |   2    |
| m3 | Description for m3 |   3    |
| m4 | Description for m4 |   4    |
| m5 | Description for m5 |   5    |
+----+--------------------+--------+

然后，您可以在该表上JOIN仅选择属于您的组的项目和/或对该组进行排序.

Then you could JOIN on that table to select only items belonging to your group and/or sort on that group.

SELECT c.id, c.channel
FROM channels c
JOIN channel_groups cg
ON cg.id = c.grp
AND c.id <> cg.id
WHERE cg.id = 'm2'
ORDER BY c.sort;

或者您可以使用类似的查询，但是可以使用WHERE子句将组频道标识为主要频道:

Or you can use a similar query, but you can identify the group channel as the primary using a WHERE clause:

SELECT c.id, c.channel, CASE WHEN c.id = cg.id THEN 1 ELSE 0 END AS primary
FROM channels c
JOIN channel_group cg
ON cg.id = c.grp
ORDER BY c.sort;

这篇关于MySQL选择从并显示不带标记的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！