On 2016/05/26 8:29 AM, Jim Wang wrote:
hi all
a table as follow:
id score
2 10
3 20
5 10
3 20
2 30
2 30
how could I select the table as follow and the count can tell me: the id 2
hava 3,the id 3 have 2 the id 5 have 1.
count id score
3 2 10
2 3 20
1 5 10
2 3 20
3 2 30
3 2 30
Hi Jim, this can work as an example:
-- SQLite version 3.9.2 [ Release: 2015-11-02 ] on SQLitespeed
version 2.0.2.4.
-- Script Items: 4 Parameter Count: 0
-- 2016-05-26 10:54:28.833 | [Info] Script Initialized,
Started executing...
--
================================================================================================
CREATE TABLE t(id INT,score INT);
INSERT INTO t(id, score) VALUES
(2, 10)
,(3, 20)
,(5, 10)
,(3, 20)
,(2, 30)
,(2, 30)
;
SELECT (SELECT count(*) FROM t AS I WHERE I.id=O.id) AS idcnt, id, score
FROM t AS O
;
-- idcnt | id | score
-- ----- | --- | -----
-- 3 | 2 | 10
-- 2 | 3 | 20
-- 1 | 5 | 10
-- 2 | 3 | 20
-- 3 | 2 | 30
-- 3 | 2 | 30
SELECT (SELECT count(*) FROM t AS I WHERE I.id=O.id) as idcnt, id, score
FROM t AS O
WHERE O.id>2
ORDER by score DESC
;
-- idcnt | id | score
-- ----- | --- | -----
-- 2 | 3 | 20
-- 2 | 3 | 20
-- 1 | 5 | 10
-- Script Stats: Total Script Execution Time: 0d 00h 00m
and 00.025s
-- Total Script Query Time: -- --- ---
--- --.----
-- Total Database Rows Changed: 6
-- Total Virtual-Machine Steps: 508
-- Last executed Item Index: 4
-- Last Script Error:
--
------------------------------------------------------------------------------------------------
HTH,
Ryan
10 points to anyone for spotting the indeterminate sort operation :D
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
