如果检查的第一列中的两行相同，如何使用另一列? [英] How do I use another column if two rows in the first column checked are the same?

问题描述

我有一张看起来像这样的桌子:

I have a table that looks something like this:

<头>

ID	数字	snumber	wt	qt
1	a		11.5	13
2	c		4.0	8
3	a	a1	1.0	2
4	d		0.5	1
5	b		2.0	4
6	a	a2	5.0	1
7	f		3.0	6
8	a	a3	5.0	10

我如何从 stones 中选择所有，如果 number 列在 snumber 列中有行，则只给出 snumber 的结果的行[否则使用编号]?

How do I select all from stones and if the column number has rows in the column snumber only give results from snumber's rows [otherwise use number]?

我还尝试在我之前的 [working] 代码中加入该代码，该代码选择表中 wt >= 2.5 的所有行，然后将所有这些行计为 totalrows:

I am also trying to incorporate in my previous [working] code that selects all rows in the table that wt >= 2.5 then count all those rows as totalrows:

SELECT inv.*, COUNT(*) OVER() AS totalrows FROM stones inv WHERE wt >= 2.5

• 因此，从上面的示例中，我想显示以下行 c、a2、f 和 a3 totalrows = 4:
• So from the example above, I'd want to show the following rows c, a2, f and a3 with totalrows = 4:
<头>

ID	数字	snumber	wt	qt	总计
2	c		4.0	8	4
6	a	a2	5.0	1	4
7	f		3.0	6	4
8	a	a3	5.0	10	4

尝试失败

我尝试了这些，但是我的 PHP 出错了，所以我假设 SQL 有问题:

Failed Attempt

I tried these, but I'm getting an error with my PHP so I'm assuming there's a problem with the SQL:

SELECT stones.*,
 CASE 
  WHEN x.totalrows > 1 
   THEN stones.snumber
   ELSE stones.number
  END AS numberORsnumber
  FROM stones 
  JOIN (
   SELECT number, COUNT(*) AS totalrows FROM stones
    GROUP BY number
  ) x ON x.number = stones.number
WHERE x.totalrows = 1 OR stones.snumber <> '' AND wt >= 2.5

第二个

SELECT inv.*, COUNT(*) OVER() AS totalrows FROM stones inv 
 CASE 
  WHEN totalrows > 1
   THEN inv.snumber
  ELSE inv.number
 END AS number
WHERE wt >= 2.5

推荐答案

错误位于 END AS number OR snumber.删除OR snumber后，不会有任何错误，但也不会有任何结果；针对您提供的示例数据.尝试将 WHERE x.totalrows = 1 AND wt >= 2.5 更改为 WHERE wt >= 2.5 只是因为您已经在执行 CASE> 表达:

The error is at END AS number OR snumber. After you remove OR snumber, there won't be any error but there won't be any result too; against the example data you've provided. Try changing WHERE x.totalrows = 1 AND wt >= 2.5 to WHERE wt >= 2.5 only because you're already doing a CASE expression:

CASE 
        WHEN x.totalrows > 1
            THEN stones.snumber
            ELSE stones.number
        END AS number

因此添加 WHERE x.totalrows = 1 不会从 CASE 中产生任何结果.

So adding WHERE x.totalrows = 1 won't yield anything from the CASE.

演示小提琴

更新:

好吧，经过长时间的评论讨论，我想我明白了:

Well, after long discussion in comment, I think I got it:

SELECT inv.ID, 
       CASE WHEN inv.snumber <> "" 
            THEN inv.snumber ELSE inv.number 
            END AS numberORSnumber, 
       inv.wt,
       inv.qt,
       COUNT(*) OVER() AS totalrows,
       CASE WHEN inv.number=v.number 
            AND inv.snumber="" THEN 0 ELSE 1 END AS Checking
FROM stones inv 
CROSS JOIN 
(SELECT number FROM stones WHERE snumber <> "" group by number) v
WHERE wt >= 2.5
HAVING Checking <> 0
ORDER BY ID ASC;

我最终添加了一个带有子查询的 CROSS JOIN，该子查询根据 OP 要求返回 number 具有 snumber 的值.然后，我使用 CASE 进行检查，最后使用 HAVING 将它们从检查中过滤掉.

I end up adding a CROSS JOIN with a subquery that return number value that have snumber as per OP requirement. Then with that, I use CASE to do the checking and finally use HAVING to filter them out from the checking.

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=40cb88da028a42dc147dc4224154ab05

这篇关于如果检查的第一列中的两行相同，如何使用另一列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！