基于另一列中的值省略具有相同id的记录的sql查询

13z8s7eq  于 2021-06-18  发布在  Mysql
关注(0)|答案(4)|浏览(296)
  • 更新*

经过进一步审查,我使用的表也有一个 linenumber 列。见下面更新的示例数据。我觉得这对解决这个问题非常有帮助…只是不知道怎么解决。把行号和po相加,如果它等于1,那就是单行,如果它大于1,那就是多行……这对我们有用吗?
我是新来的,所以请原谅我的无知。希望这是一个简单的答案。
希望构建3个类似的查询,这些查询将返回包含1个以上项目的采购订单,并且:
不包含批次控制项
包含所有批次控制项
包含批次控制和非批次控制项目的混合
数据看起来像这样。。。

PONUMBER    ITEMNUMBER  LOTCONTROLLED  LINENUMBER
PO1.18      OSC1024     0              1
PO1.18      OSC1025     0              2
PO1.18      OSC1026     0              3
PO1.2       OSC1199     0              1
PO1.2       OSC1200     1              2
PO1.21      OSC1201     1              1
PO1.21      OSC1202     1              2
PO1.22      OSC1203     1              1
PO1.23      OSC1204     1              1
PO1.23      OSC1205     0              2
PO1.24      OSC1206     1              1
PO1.24      OSC1207     1              2
PO1.24      OSC1300     0              3

没有批次控制项目的查询工作得很好。。。

SELECT 
      `POD`.`PONUMBER`,
      `POD`.`ITEMNUMBER`,
      `POD`.`LOTCONTROLLED`
    FROM
      table1 AS `POD`
    INNER JOIN
      (
        SELECT `PONUMBER`, COUNT(`PONUMBER`)
        FROM table1
        WHERE `LOTCONTROLLED` = 0
        GROUP BY `PONUMBER`
        HAVING (COUNT(`PONUMBER`) > 1)
       ) as `POD1`
     ON `POD`.`PONUMBER` = `POD1`.`PONUMBER`

我想这会很简单 WHERE LOTCONTROLLED to be=1,获取包含所有批次控制项的采购订单,但这会返回一些具有混合行的采购订单。
如果任何一行没有进行批量控制,我如何从包含的采购订单中删除?

w6lpcovy

w6lpcovy1#

尝试以下操作:

--No items in the group contain LotControlled 
    SELECT * 
    FROM   your_table 
    WHERE  ponumber IN (SELECT ponumber 
                        FROM   your_table 
                        GROUP  BY ponumber 
                        HAVING Sum(CONVERT(INT, lotcontrolled)) = 0) 

    --All Items Contain 
    SELECT * 
    FROM   your_table 
    WHERE  ponumber IN (SELECT ponumber 
                        FROM   your_table 
                        GROUP  BY ponumber 
                        HAVING Sum(CONVERT(INT, lotcontrolled)) = Count(*)) 

    --mixed 
    SELECT * 
    FROM   your_table 
    WHERE  ponumber IN (SELECT ponumber 
                        FROM   your_table 
                        GROUP  BY ponumber 
                        HAVING Sum(CONVERT(INT, lotcontrolled)) != Count(*) 
                               AND Sum(CONVERT(INT, lotcontrolled)) > 0)
f8rj6qna

f8rj6qna2#

我喜欢用 NOT EXISTS 在这里:

SELECT POD.*
FROM table1 POD
JOIN (SELECT PONUMBER
      FROM table1 POD
      WHERE NOT EXISTS (SELECT *
                        FROM table1 POD1
                        WHERE POD.PONUMBER = POD1.PONUMBER
                        AND POD1.LOTCONTROLLED = 1)
      GROUP BY PONUMBER
      HAVING COUNT(*) > 1
      ) POD1 ON POD.PONUMBER = POD1.PONUMBER

这将省略 PONUMBER 如果有记录的话 PONUMBERLOTCONTROLLED =1或0,这取决于您在exists子查询中输入的内容。
要仅获取具有混合的记录,可以使用 COUNT().. HAVING :

SELECT PONUMBER,
       ITEMNUMBER,
       LOTCONTROLLED
FROM table1 POD
JOIN (SELECT PONUMBER
      FROM table1
      GROUP BY PONUMBER
      HAVING COUNT(DISTINCT LOTCONTROLLED) = 2
     ) POD1 ON POD.PONUMBER = POD1.PONUMBER
bq9c1y66

bq9c1y663#

窗口函数是最简单的方法,但您可能没有这些方法。所以,就用 min() 以及 max()lotcontrolled . 基本查询是:

select pod.* 
from table1 pod join
      (select ponumber, min(lotcontrolled) as min_lc, max(lotcontrolled) as max_lc
       from table1 pod
       group by ponumber
       having count(*) > 1
      ) p
      using (ponumber)

那么你的三个条件是:

max_lc = 0  -- no lot controlled
min_lc = 1  -- all lot controlled 
min_lc <> max_lc  -- mixed

有些人可能更喜欢更详细的版本:

min_lc = max_lc and max_lc = 0  -- no lot controlled
min_lc = max_lc and max_lc = 1  -- all lot controlled 
min_lc <> max_lc  -- mixed
rqdpfwrv

rqdpfwrv4#

看起来您还需要按lot controlled连接查询,因此我将其添加到group by和inner select中,以便可以连接:

无批次控制:

SELECT 
      `POD`.`PONUMBER`,
      `POD`.`ITEMNUMBER`,
      `POD`.`LOTCONTROLLED`
    FROM
      table1 AS `POD`
    INNER JOIN
      (
        SELECT `PONUMBER`, 'LOTCONTROLLED', COUNT(`PONUMBER`)
        FROM table1
        WHERE `LOTCONTROLLED` = 0
        GROUP BY `PONUMBER`, 'LOTCONTROLLED'
        HAVING (COUNT(`PONUMBER`) > 1)
       ) as `POD1`
     ON `POD`.`PONUMBER` = `POD1`.`PONUMBER` AND `POD`.`LOTCONTROLLED` = `POD1`.`LOTCONTROLLED`

控制批次:

SELECT 
      `POD`.`PONUMBER`,
      `POD`.`ITEMNUMBER`,
      `POD`.`LOTCONTROLLED`
    FROM
      table1 AS `POD`
    INNER JOIN
      (
        SELECT `PONUMBER`, 'LOTCONTROLLED', COUNT(`PONUMBER`)
        FROM table1
        WHERE `LOTCONTROLLED` = 1
        GROUP BY `PONUMBER`, 'LOTCONTROLLED'
        HAVING (COUNT(`PONUMBER`) > 1)
       ) as `POD1`
     ON `POD`.`PONUMBER` = `POD1`.`PONUMBER` AND `POD`.`LOTCONTROLLED` = `POD1`.`LOTCONTROLLED`

所有批次控制:

SELECT 
      `POD`.`PONUMBER`,
      `POD`.`ITEMNUMBER`,
      `POD`.`LOTCONTROLLED`
    FROM
      table1 AS `POD`
    INNER JOIN
      (
        SELECT `PONUMBER`, 'LOTCONTROLLED', COUNT(`PONUMBER`)
        FROM table1
        WHERE `LOTCONTROLLED` IN (0,1)
        GROUP BY `PONUMBER`, 'LOTCONTROLLED'
        HAVING (COUNT(`PONUMBER`) > 1)
       ) as `POD1`
     ON `POD`.`PONUMBER` = `POD1`.`PONUMBER` AND `POD`.`LOTCONTROLLED` = `POD1`.`LOTCONTROLLED`

相关问题