CREATE TABLE aa(account VARCHAR(32),ip VARCHAR(32),type NUMBER,address VARCHAR(32));insert into aa(account, ip, type, address) values('wang','192.168.12.1',1,'beijing');
insert into aa(account, ip, type, address) values('wang','192.168.12.2',1,'hangzhou');
insert into aa(account, ip, type, address) values('wang','192.168.12.3',1,'shanghai');
insert into aa(account, ip, type, address) values('fang','192.168.12.1',1,'beijing');
insert into aa(account, ip, type, address) values('fang','192.168.12.1',1,'beijing');
insert into aa(account, ip, type, address) values('liu','192.168.12.6',1,'beijing');
insert into aa(account, ip, type, address) values('liu','192.168.12.6',1,'beijing');
insert into aa(account, ip, type, address) values('liu','192.168.12.6',1,'beijing');
insert into aa(account, ip, type, address) values('liu','192.168.12.6',1,'beijing');
insert into aa(account, ip, type, address) values('liu','192.168.12.6',1,'beijing');我想把所有的字段都查出来,但是要以account来分组。
在oracle中用这样的sql:
SELECT ACCOUNT, ip, TYPE, address
FROM aa
GROUP BY ACCOUNT;
提示“不是group by表达式”有没有什么办法规避这样的问题。最后查询出来的结果中,除了account字段外的其他字段都可以随机取值,有这样的函数吗?在sqllite中这样的sql语句就没有问题
insert into aa(account, ip, type, address) values('wang','192.168.12.2',1,'hangzhou');
insert into aa(account, ip, type, address) values('wang','192.168.12.3',1,'shanghai');
insert into aa(account, ip, type, address) values('fang','192.168.12.1',1,'beijing');
insert into aa(account, ip, type, address) values('fang','192.168.12.1',1,'beijing');
insert into aa(account, ip, type, address) values('liu','192.168.12.6',1,'beijing');
insert into aa(account, ip, type, address) values('liu','192.168.12.6',1,'beijing');
insert into aa(account, ip, type, address) values('liu','192.168.12.6',1,'beijing');
insert into aa(account, ip, type, address) values('liu','192.168.12.6',1,'beijing');
insert into aa(account, ip, type, address) values('liu','192.168.12.6',1,'beijing');我想把所有的字段都查出来,但是要以account来分组。
在oracle中用这样的sql:
SELECT ACCOUNT, ip, TYPE, address
FROM aa
GROUP BY ACCOUNT;
提示“不是group by表达式”有没有什么办法规避这样的问题。最后查询出来的结果中,除了account字段外的其他字段都可以随机取值,有这样的函数吗?在sqllite中这样的sql语句就没有问题
SELECT ACCOUNT, ip, TYPE, address
FROM aa
ORDER BY ACCOUNT;
m.ip,
m.TYPE,
m.address
FROM (SELECT t.*,
row_number() OVER(PARTITION BY ACCOUNT ORDER BY ip) cnt
FROM aa t) m
WHERE cnt = 1sqllite不报错是因为group by 取第1条了。
你对account 进行分组后,其他的 如ip,type,address只能用 max,min,count 等等取一个值的函数。
FROM aa
GROUP BY ACCOUNT;