Mysql4：用于选择一个或零记录的SQL [英] Mysql4: SQL for selecting one or zero record

问题描述

表格布局：

  CREATE TABLE t_order（id INT，custId INT，order DATE）
  

我正在寻找一个SQL命令来为每个订单最多选择一行（拥有订单的客户由

我想选择客户的订单中的一个（无论哪一个，请按id排序）if没有为任何行提供订单日期。

如果已经有给定订单日期的记录，我想为customerId检索一个空的Resultset。 / p>

这是一个例子。每个客户最多应该有一个订单（一个没有给定日期）。已经有日期值的订单不应出现。

 
 + -------------- ------------------------------------------- + 
 | id | custId |日期| 
 + --------------------------------------------- ------------ + 
 | 1 10 NULL | 
 | 2 11 2008-11-11 | 
 | 3 12 2008-10-23 | 
 | 4 11 NULL | 
 | 5 13 NULL | 
 | 6 13 NULL | 
 + --------------------------------------------- ------------ + 
 | 
 | 
 |结果
 \ | / 
 \ / 
 + ------------------------------------- -------------------- + 
 | id | custId |日期| 
 + --------------------------------------------- ------------ + 
 | 1 10 NULL | 
 | | 
 | | 
 | | 
 | 5 13 NULL | 
 | | 
 + --------------------------------------------- ------------ + 
 powered as JavE 
 

编辑：
I' ve choosenglavić的答案是正确的，因为它提供
正确的结果与稍微修改的数据：

 
 + --- -------------------------------------------------- ---- + 
 | id | custId |日期| 
 + --------------------------------------------- ------------ + 
 | 1 10 NULL | 
 | 2 11 2008-11-11 | 
 | 3 12 2008-10-23 | 
 | 4 11 NULL | 
 | 5 13 NULL | 
 | 6 13 NULL | 
 | 7 11 NULL | 
 + --------------------------------------------- ------------ + 
 

当客户出现两次以上时，Sfossen的回答不会工作，因为它的位置这是因为我运行服务器版本4.0.24，这会产生以下错误：





$ b alt text http://img25.imageshack.us/img25/8186/picture1vyj。 png

解决方案

尝试：

  SELECT to1。* 
 FROM t_order AS to1 
 WHERE 
 to1.date IS NULL AND 
 to1.custId NOT IN（
 SELECT to2 .custId 
 FROM t_order AS to2 
 WHERE to2.date IS NOT NULL 
 GROUP BY to2.custId 
）
 GROUP BY to1.custId 
  

对于MySQL 4：

  SELECT to1。* 
 FROM t_order AS to1 
 LEFT JOIN t_order AS to2 ON 
 to2.custId = to1.custId AND 
 to2.date IS NOT NULL 
 WHERE 
 to1.date IS NULL AND 
 to2.id IS NULL 
 GROUP BY to1.custId 
  




Table layout:

CREATE TABLE t_order  (id INT, custId INT, order DATE)

I'm looking for a SQL command to select a maximum of one row per order (the customer who owns the order is identified by a field named custId).

I want to select ONE of the customer's orders (doesn't matter which one, say sorted by id) if there is no order date given for any of the rows.

I want to retrieve an empty Resultset for the customerId, if there is already a record with given order date.

Here is an example. Per customer there should be one order at most (one without a date given). Orders that have already a date value should not appear at all.

  +---------------------------------------------------------+
  |id    |    custId       |  date                          |
  +---------------------------------------------------------+
  | 1              10         NULL                          |
  | 2              11         2008-11-11                    |
  | 3              12         2008-10-23                    |
  | 4              11         NULL                          |
  | 5              13         NULL                          |
  | 6              13         NULL                          |
  +---------------------------------------------------------+
                           |
                           |
                           |      Result
                         \ |  /
                          \  /
  +---------------------------------------------------------+
  |id    |    custId       |  date                          |
  +---------------------------------------------------------+
  | 1              10         NULL                          |
  |                                                         |
  |                                                         |
  |                                                         |
  | 5              13         NULL                          |
  |                                                         |
  +---------------------------------------------------------+
                                powered be JavE

Edit: I've choosen glavić's answer as the correct one, because it provides the correct result with slightly modified data:

  +---------------------------------------------------------+
  |id    |    custId       |  date                          |
  +---------------------------------------------------------+
  | 1              10         NULL                          |
  | 2              11         2008-11-11                    |
  | 3              12         2008-10-23                    |
  | 4              11         NULL                          |
  | 5              13         NULL                          |
  | 6              13         NULL                          |
  | 7              11         NULL                          |
  +---------------------------------------------------------+

Sfossen's answer will not work when customers appear more than twice because of its where clause constraint a.id != b.id.

Quassnoi's answer does not work for me, as I run server version 4.0.24 which yields the following error: alt text http://img25.imageshack.us/img25/8186/picture1vyj.png

解决方案

Try this:

SELECT to1.*
FROM t_order AS to1
WHERE
    to1.date IS NULL AND 
    to1.custId NOT IN (
        SELECT to2.custId
        FROM t_order AS to2
        WHERE to2.date IS NOT NULL
        GROUP BY to2.custId
    )
GROUP BY to1.custId

For MySQL 4:

SELECT to1.*
FROM t_order AS to1
LEFT JOIN t_order AS to2 ON
    to2.custId = to1.custId AND
    to2.date IS NOT NULL
WHERE
    to1.date IS NULL AND 
    to2.id IS NULL
GROUP BY to1.custId

这篇关于Mysql4：用于选择一个或零记录的SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！