This topic has been archived. It cannot be replied.
-
工作学习 / IT技术讨论 / 帮忙写个简单的sql, 我不会。:(我需要挑选出一些特定的纪录，group by (a,b,c) 然后group by (a,b,d), 但是，用in 的时候，前面怎么才能写两个field呢，a和b.
因为a和b在一起才能决定一行。
SELECT distinct a
FROM Table1
where a in (
SELECT a
FROM Table1
GROUP BY a, b, c
HAVING (COUNT(*) = 1))
and a in (SELECT a
FROM Table1
GROUP BY a, b, d
HAVING (COUNT(*) = 1))
-lilyba(sunshine);
2004-8-10
{381}
(#1836557@0)
-
不好意思。你最终结果想要什么？
-henhen(亨亨 ^_*);
2004-8-10
{20}
(#1836561@0)
-
举个例子: a 是个物品，b=出产国, c=taste. d=shape. 我现在要挑出的纪录必须是一定国家出产的物品，他的taste或shape只有一种。
-lilyba(sunshine);
2004-8-10
(#1836596@0)
-
select distinct a, b from (
select distinct a, b, c from table1 group by a, b,c having count(*) = 1
union
select distinct a,b,d from table1 group by a,b,d having count(*) = 1)
-mengxiong(mengxiong);
2004-8-11
(#1837873@0)
-
it is "and" not "or", so union won't work
-guestagain(guest again);
2004-8-11
(#1837977@0)
-
then union all
-mengxiong(mengxiong);
2004-8-11
(#1838157@0)
-
nice try, but...
-guestagain(guest again);
2004-8-11
(#1838230@0)
-
粒粒霸现在越来越老辣了, 开口就来个简单的....是什么数据库平台呢...MS?
-expertune(伪劣);
2004-8-10
(#1836562@0)
-
sql server.
-lilyba(sunshine);
2004-8-10
(#1836581@0)
-
SELECT a FROM Table1GROUP BY a, b, c ? mysql?
-guestagain(guest again);
2004-8-10
(#1836574@0)
-
大虾，看看楼上我说清楚了么。当然a的datatype是uniqueidentifier。
-lilyba(sunshine);
2004-8-10
(#1836600@0)
-
既然是unique identifier,为什么还要用distinct?你说的两个field, 是指
where a, b in (...) 吗？
-lifeforest(forest);
2004-8-10
{47}
(#1836606@0)
-
a是uniqueidentifier, but not primary key。 where a, b in (...) 这种形式不工作吧。不知道是不是可以把uniqueidentifier转化成string, 然后来个where a.tostring+b.tostring in (select a.tostring+b.tostring ....)
-lilyba(sunshine);
2004-8-10
(#1836609@0)
-
trySELECT distinct a
FROM Table1,
((SELECT a,b FROM Table1 GROUP BY a, b, c HAVING (COUNT(*) = 1)
UNION
(SELECT a,b FROM Table1 GROUP BY a, b, d HAVING (COUNT(*) = 1)) temp
where table1.a= temp.a
and table1.b = temp.b
-guestagain(guest again);
2004-8-10
{267}
(#1836610@0)
-
好像明白了。我看看。我怎么能想出那么绕的办法来实现呢。：（
-lilyba(sunshine);
2004-8-10
(#1836613@0)
-
sorry, should be INTERSECT instead of UNION
-guestagain(guest again);
2004-8-10
(#1836619@0)
-
谢谢，可能比你写的要复杂些。我实践一下。
-lilyba(sunshine);
2004-8-10
(#1836623@0)
-
偶也谢谢你，没用过intersect，今天学了一招。顺便问问， 有个table有几万条记录甚至20多万条记录，象这样
typeid mid tag statusid finaltypeid
其它表分别是， 都不是很大
typeid typename
;
finaltypeid finaltypename
,
mid mname
;
sid mid statusid;
;
stutasid statusname
实际涉及的还要多些，
最后要
typename, tag, mname, statusname, finaltypename
有什么办法使query快些吗？
-henhen(亨亨 ^_*);
2004-8-10
{336}
(#1836633@0)
-
sorry, I am not a DBA. I feel some index will help but you have to check the query plan from your server and find out where is the bottleneck.
-guestagain(guest again);
2004-8-10
(#1836962@0)
-
intersect 在sql server 里面不工作，可能只能用cursor乐。 ：（
-lilyba(sunshine);
2004-8-11
(#1837337@0)
-
then trySELECT distinct table1.a
FROM Table1,
(SELECT a,b FROM Table1 GROUP BY a, b, c HAVING (COUNT(*) = 1)) t1,
(SELECT a,b FROM Table1 GROUP BY a, b, d HAVING (COUNT(*) = 1)) t2
where table1.a= t1.a
and table1.b = t1.b
and t1.a = t2.a
and t1.b = t2.b
-guestagain(guest again);
2004-8-11
{254}
(#1837523@0)
-
That is what i am doing now. 英雄所见略同。还真不能说是个简单的query. :P
-lilyba(sunshine);
2004-8-11
(#1837600@0)
-
没看懂，能不能写个简单的例子。
-lifeforest(forest);
2004-8-10
(#1836597@0)
-
没看懂nested query的必要性。SELECT DISTINCT A FROM TABLE1 GROUP BY A, B, C HAVING (COUNT(*)=1) UNION SELECT A FROM TABLE1 GROUP BY A, B, D HAVING (COUNT(*)=1)
-23456789(大白呼);
2004-8-11
(#1836787@0)