表结构如下,主键是由两个列组合而成的CREATE TABLE t1 ( id CHAR(2 BYTE) NOT NULL, xid CHAR(2 BYTE) NOT NULL, name varchar2(10), unit varchar2(5), price number(2, 2) ) ALTER TABLE t1 ADD ( CONSTRAINT t1_PK PRIMARY KEY (id, xid)); 给几条示例数据: id xid name unit price 01 01 橘子 泡 10 01 02 橘子 泡 20 01 03 橘子 泡 29 01 04 橘子 泡 23 02 01 葡萄 粒 34 02 03 葡萄 粒 21 03 01 榴莲 根 21 03 02 榴莲 根 25 结果需要 id name unit price 01 橘子 泡 10 02 葡萄 粒 34 03 榴莲 根 21
price number(2, 2) 只能存小数了...这个查询最好使用exists来实现,但这大概就是你说的子查询 要不就用group bySELECT ID,NAME,unit, MAX(price)KEEP(dense_rank FIRST ORDER BY XID) price FROM t1 GROUP BY ID,NAME,unit;
我弄错了,谢谢[Quoto] SELECT ID,NAME,unit, MAX(price)KEEP(dense_rank FIRST ORDER BY XID) price FROM t1 GROUP BY ID,NAME,unit; [/Quoto] 请问KEEP是什么? 是分析函数吗? 这个我有些不熟悉。。 再有,KEEP要是成功的话,也只能在ORACLE里面使用啊,换到别的地方就不能使用了。谢谢
那就用 select * from t1 t where not exists( select 1 from t1 where id=t.id and xid<t.xid); 这个比较通用
select * from (select rank()over(partition by name order by name,xid) rk,a.* from t1 a order by id) where rk=1;
select id,name,unit,price from (select id,name,unit,price,row_number() over(partition by id order by xid) rn from t1) a where rn=1--2 select id,name,unit,price from t1 a where exists(select 1 from t1 where a.id=id and a.xid<xid)
row_number()over(partition by id order by xid ) as n ,取 n = 1
drop table t1; CREATE TABLE t1 ( id CHAR(2 BYTE) NOT NULL, xid CHAR(2 BYTE) NOT NULL, name varchar2(10), unit varchar2(5), price int ) ; ALTER TABLE t1 ADD ( CONSTRAINT t1_PK PRIMARY KEY (id, xid));
insert into t1 select '01','01','橘子','泡',10 from dual union all select '01','02','橘子','泡',20 from dual union all select '01','03','橘子','泡',29 from dual union all select '01','04','橘子','泡',23 from dual union all select '02','01','葡萄','粒',34 from dual union all select '02','03','葡萄','粒',21 from dual union all select '03','01','榴莲','根',21 from dual union all select '03','02','榴莲','根',10 from dual; commit;--SQL如下select id, --min(xid) as xid, chr(max(ascii(name))) || chr(max(ascii(substr(name, 2, 2)))), chr(max(ascii(unit))) as unit, substr(min(xid||price),3,4) as price from t1 group by id 看看这个,呵呵。没有子查询吧,哈哈。
select id,name,unit,min(price) from t1 group by name,unit,idmysql可以执行正常
select id,name,unit,price from t1 where xid='01'囧我怎么觉得这样貌似就解决了
碰巧xid比较规律而已,如果数据多的话,xid是没有规律的
这样的sql不用子查询貌似没法实现。
drop table t1; CREATE TABLE t1 ( id CHAR(2 BYTE) NOT NULL, xid CHAR(2 BYTE) NOT NULL, name varchar2(10), unit varchar2(5), price int ) ; ALTER TABLE t1 ADD ( CONSTRAINT t1_PK PRIMARY KEY (id, xid));
insert into t1 select '01','01','橘子','泡',10 from dual union all select '01','02','橘子','泡',20 from dual union all select '01','03','橘子','泡',29 from dual union all select '01','04','橘子','泡',23 from dual union all select '02','01','葡萄','粒',34 from dual union all select '02','03','葡萄','粒',21 from dual union all select '03','01','榴莲','根',21 from dual union all select '03','02','榴莲','根',10 from dual; commit; select r.id,r.Name,r.unit,r.price from t1 r, (select a.id,min(a.xid) g from t1 a group by a.id) e where r.id = e.id and r.xid = e.g不知道你的 xid是不是从小开始排的。
With t1 As( Select 01 Id, 01 Xid, '橘子' Name, '泡' unit, 10 price From dual Union Select 01 , 02 , '橘子', '泡' , 20 From dual Union Select 01 , 03 ,'橘子', '泡' , 29 From dual Union Select 01 , 04 , '橘子', '泡', 23 From dual Union Select 02 , 01 , '葡萄' , '粒', 34 From dual Union Select 02 , 03 , '葡萄', '粒' , 21 From dual Union Select 03 , 01 , '榴莲' , '根' , 21 From dual Union Select 03 , 02, '榴莲' , '根', 25 From dual ) select id,name,unit,price from (select id,name,unit,price,row_number() over(partition by id order by xid) rn from t1) a where rn=1;
就楼主的需求只要这样差就ok了:select id,name,unit,price from t1 WHERE XID=1最简洁的方法
, substr(min(t.id||','||rownum||','||t.name||','||t.unit||','||t.price),instr(min(t.id||','||rownum||','||t.name||','||t.unit||','||t.price),',',1,4)+1 )price from t1 t group by t.id
, substr(min(t.id||','||(rownum+10000000000000000000000000)||','||t.name||','||t.unit||','||t.price),instr(min(t.id||','||(rownum+10000000000000000000000000)||','||t.name||','||t.unit||','||t.price),',',1,4)+1 )price from t1 t group by t.id
-- 有 where 条件的话,不用子查询几乎是无法实现的!
我测试了你的语句,通过where rownum<2 返回的整个表格的第一条数据。而且rownum好像没法和group by混合使用嘛。请继续,谢谢
表结构如下,主键是由两个列组合而成的CREATE TABLE t1
(
id CHAR(2 BYTE) NOT NULL,
xid CHAR(2 BYTE) NOT NULL,
name varchar2(10),
unit varchar2(5),
price number(2, 2)
)
ALTER TABLE t1 ADD (
CONSTRAINT t1_PK
PRIMARY KEY
(id, xid));
给几条示例数据: id xid name unit price
01 01 橘子 泡 10
01 02 橘子 泡 20
01 03 橘子 泡 29
01 04 橘子 泡 23
02 01 葡萄 粒 34
02 03 葡萄 粒 21
03 01 榴莲 根 21
03 02 榴莲 根 25
结果需要 id name unit price
01 橘子 泡 10
02 葡萄 粒 34
03 榴莲 根 21
只能存小数了...这个查询最好使用exists来实现,但这大概就是你说的子查询
要不就用group bySELECT ID,NAME,unit,
MAX(price)KEEP(dense_rank FIRST ORDER BY XID) price
FROM t1 GROUP BY ID,NAME,unit;
SELECT ID,NAME,unit,
MAX(price)KEEP(dense_rank FIRST ORDER BY XID) price
FROM t1 GROUP BY ID,NAME,unit;
[/Quoto]
请问KEEP是什么? 是分析函数吗? 这个我有些不熟悉。。
再有,KEEP要是成功的话,也只能在ORACLE里面使用啊,换到别的地方就不能使用了。谢谢
select * from t1 t
where not exists(
select 1 from t1 where id=t.id and xid<t.xid);
这个比较通用
from (select rank()over(partition by name order by name,xid) rk,a.* from t1 a
order by id)
where rk=1;
select id,name,unit,price
from (select id,name,unit,price,row_number() over(partition by id order by xid) rn
from t1) a
where rn=1--2
select id,name,unit,price from t1 a where exists(select 1 from t1 where a.id=id and a.xid<xid)
drop table t1;
CREATE TABLE t1
(
id CHAR(2 BYTE) NOT NULL,
xid CHAR(2 BYTE) NOT NULL,
name varchar2(10),
unit varchar2(5),
price int
)
;
ALTER TABLE t1 ADD (
CONSTRAINT t1_PK
PRIMARY KEY
(id, xid));
insert into t1 select '01','01','橘子','泡',10 from dual union all
select '01','02','橘子','泡',20 from dual union all
select '01','03','橘子','泡',29 from dual union all
select '01','04','橘子','泡',23 from dual union all
select '02','01','葡萄','粒',34 from dual union all
select '02','03','葡萄','粒',21 from dual union all
select '03','01','榴莲','根',21 from dual union all
select '03','02','榴莲','根',10 from dual;
commit;--SQL如下select id,
--min(xid) as xid,
chr(max(ascii(name))) || chr(max(ascii(substr(name, 2, 2)))),
chr(max(ascii(unit))) as unit,
substr(min(xid||price),3,4) as price
from t1
group by id
看看这个,呵呵。没有子查询吧,哈哈。
drop table t1;
CREATE TABLE t1
(
id CHAR(2 BYTE) NOT NULL,
xid CHAR(2 BYTE) NOT NULL,
name varchar2(10),
unit varchar2(5),
price int
)
;
ALTER TABLE t1 ADD (
CONSTRAINT t1_PK
PRIMARY KEY
(id, xid));
insert into t1 select '01','01','橘子','泡',10 from dual union all
select '01','02','橘子','泡',20 from dual union all
select '01','03','橘子','泡',29 from dual union all
select '01','04','橘子','泡',23 from dual union all
select '02','01','葡萄','粒',34 from dual union all
select '02','03','葡萄','粒',21 from dual union all
select '03','01','榴莲','根',21 from dual union all
select '03','02','榴莲','根',10 from dual;
commit;
select r.id,r.Name,r.unit,r.price from t1 r,
(select a.id,min(a.xid) g from t1 a group by a.id) e
where r.id = e.id and r.xid = e.g不知道你的 xid是不是从小开始排的。
Select 01 Id, 01 Xid, '橘子' Name, '泡' unit, 10 price From dual
Union
Select 01 , 02 , '橘子', '泡' , 20 From dual
Union
Select 01 , 03 ,'橘子', '泡' , 29 From dual
Union
Select 01 , 04 , '橘子', '泡', 23 From dual
Union
Select 02 , 01 , '葡萄' , '粒', 34 From dual
Union
Select 02 , 03 , '葡萄', '粒' , 21 From dual
Union
Select 03 , 01 , '榴莲' , '根' , 21 From dual
Union
Select 03 , 02, '榴莲' , '根', 25 From dual
)
select id,name,unit,price
from (select id,name,unit,price,row_number() over(partition by id order by xid) rn
from t1) a
where rn=1;
就楼主的需求只要这样差就ok了:select id,name,unit,price
from t1
WHERE XID=1最简洁的方法
最好用ROWNUM作为标识符,借用13楼的想法,也写了一条SQL。
select substr(min(t.id||','||rownum||','||t.name||','||t.unit||','||t.price),0,instr(min(t.id||','||rownum||','||t.name||','||t.unit||','||t.price),',',1,1)-1
) id
,
substr(min(t.id||','||rownum||','||t.name||','||t.unit||','||t.price),instr(min(t.id||','||rownum||','||t.name||','||t.unit||','||t.price),',',1,2)+1
,instr(min(t.id||','||rownum||','||t.name||','||t.unit||','||t.price),',',1,3)-
instr(min(t.id||','||rownum||','||t.name||','||t.unit||','||t.price),',',1,2)-1
)name
,
substr(min(t.id||','||rownum||','||t.name||','||t.unit||','||t.price),instr(min(t.id||','||rownum||','||t.name||','||t.unit||','||t.price),',',1,3)+1
,instr(min(t.id||','||rownum||','||t.name||','||t.unit||','||t.price),',',1,4)-
instr(min(t.id||','||rownum||','||t.name||','||t.unit||','||t.price),',',1,3)-1
)unit
,
substr(min(t.id||','||rownum||','||t.name||','||t.unit||','||t.price),instr(min(t.id||','||rownum||','||t.name||','||t.unit||','||t.price),',',1,4)+1 )price
from t1 t group by t.id
select substr(min(t.id||','||(rownum+10000000000000000000000000)||','||t.name||','||t.unit||','||t.price),0,instr(min(t.id||','||(rownum+10000000000000000000000000)||','||t.name||','||t.unit||','||t.price),',',1,1)-1
) id
,
substr(min(t.id||','||(rownum+10000000000000000000000000)||','||t.name||','||t.unit||','||t.price),instr(min(t.id||','||(rownum+10000000000000000000000000)||','||t.name||','||t.unit||','||t.price),',',1,2)+1
,instr(min(t.id||','||(rownum+10000000000000000000000000)||','||t.name||','||t.unit||','||t.price),',',1,3)-
instr(min(t.id||','||(rownum+10000000000000000000000000)||','||t.name||','||t.unit||','||t.price),',',1,2)-1
)name
,
substr(min(t.id||','||(rownum+10000000000000000000000000)||','||t.name||','||t.unit||','||t.price),instr(min(t.id||','||(rownum+10000000000000000000000000)||','||t.name||','||t.unit||','||t.price),',',1,3)+1
,instr(min(t.id||','||(rownum+10000000000000000000000000)||','||t.name||','||t.unit||','||t.price),',',1,4)-
instr(min(t.id||','||(rownum+10000000000000000000000000)||','||t.name||','||t.unit||','||t.price),',',1,3)-1
)unit
,
substr(min(t.id||','||(rownum+10000000000000000000000000)||','||t.name||','||t.unit||','||t.price),instr(min(t.id||','||(rownum+10000000000000000000000000)||','||t.name||','||t.unit||','||t.price),',',1,4)+1 )price
from t1 t group by t.id