How to write this query - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

How to write this query

How to write this query# Database - 数据库

l*u2007-09-06 07:09

1 楼

OrderNo lineNo Status LineType Date1 Date2
1 0001 99 T 07092002(date1)
1 0002 99 S 07012002(date2)
1 0003 50 S
2 0001 99 S 07012002(date2)
2 0002 99 S 07012002(date2)
3 0001 50 T 07092002(date1)
3 0002 99 S 0

t*g2007-09-06 07:09

2 楼

用两个not in在你的where clause里面应该可以作出来

【在 l********u 的大作中提到】

: OrderNo lineNo Status LineType Date1 Date2
: 1 0001 99 T 07092002(date1)
: 1 0002 99 S 07012002(date2)
: 1 0003 50 S
: 2 0001 99 S 07012002(date2)
: 2 0002 99 S 07012002(date2)
: 3 0001 50 T 07092002(date1)
: 3 0002 99 S 0

x*e2007-09-06 07:09

3 楼

"in" is OK, "not in" is not efficient.

c*n2007-09-06 07:09

4 楼

2 LEFF JOIN

B*g2007-09-06 07:09

5 楼

***Ban Er Zhuan is welcome***
1. why orderno 3 is in 08/30/02--09/10/02?
2.please provide database structure and index for a better solution
3. my sql statemnt (change date to 06/30/02--09/10/02)
SELECT *
FROM TBL t1
WHERE NOT EXISTS ( SELECT 1
FROM TBL t2
WHERE t2.OrderNo = t1.OrderNo
AND (
(((t2.Status != 99) OR (t2.Status IS NULL))
AND ((t2.LineType != 'T') OR (t2.LineType IS NULL)))

【在 l********u 的大作中提到】