c***y
发帖数: 114 | 1 有一个大table把数据都浑在一起了, 结构酱紫:
BookId DataName DataValue
1 Title t1
1 Author a1
1 Publisher p1
2 Title t2
2 Author a2
2 Publisher p2
3 Author a3
3 Publisher p3
如何把所有没有Title的BookId选出来, 不是选Title值为空的, 而是根本就没有这一行的
//bow |
s***e
发帖数: 284 | 2 (select distinct bookid from table) except
(select distinct bookid from table where dataname = 'title' )
【在 c***y 的大作中提到】
: 有一个大table把数据都浑在一起了, 结构酱紫:
: BookId DataName DataValue
: 1 Title t1
: 1 Author a1
: 1 Publisher p1
: 2 Title t2
: 2 Author a2
: 2 Publisher p2
: 3 Author a3
: 3 Publisher p3
|
c***y
发帖数: 114 | 3 thanks!
hmm.. seems mysql doesn't support EXCEPT, doesn't support nested queries
neither
【在 s***e 的大作中提到】
: (select distinct bookid from table) except
: (select distinct bookid from table where dataname = 'title' )
|
s***e
发帖数: 284 | 4 I know nothing about mysql. hehe.
Just checked its documents, it seems mysql supports subqueries now.
If so, you could write in this way:
select distinct bookid from table where
bookid not in ( select distinct bookid from table where dataname = 'title')
【在 c***y 的大作中提到】
: thanks!
: hmm.. seems mysql doesn't support EXCEPT, doesn't support nested queries
: neither
|
c***y
发帖数: 114 | 5 thanks
it supports nested queries from 4.0, i'm using 3.21 :(
i figured it out by store the output from sub-query into an array
【在 s***e 的大作中提到】
: I know nothing about mysql. hehe.
: Just checked its documents, it seems mysql supports subqueries now.
: If so, you could write in this way:
: select distinct bookid from table where
: bookid not in ( select distinct bookid from table where dataname = 'title')
|
r****y
发帖数: 26819 | 6 use NOT EXISTS
【在 c***y 的大作中提到】
: thanks!
: hmm.. seems mysql doesn't support EXCEPT, doesn't support nested queries
: neither
|
