求一复杂sql语句

Question

id stucode stuaddress sturoom stutime
1 22 35# n502 201005   
2 22 35# n502 201007
3 12 45# n501 201005
4 22 35# n502 201005
5 22 35# n502 201005
6 12 45# n501 201009
7 12 65# n401 201010
8 22 35# n502 201005
9 22 34# n502 201005
10 32 35# n512 201005
11 22 34# n502 201007
12 32 55# n512 201009
13 33 55# n512 201009
求一sql语句得到结果如下:
code address room time
22 35# n502 201007
12 45# n501 201009
32 55# n512 201009
33 55# n512 201009code为基础,然后对比address+room 把相同条目多的留下,最后只留下一条time最大的.原则一:22的 35#+n502 有4条, 22的34#+n502 有2条 所以留下22的 35#+n502,然后再剩下时间最大的一条
22 35# n502 201007
原则二:32的55#+n512 有1条, 32的35#+n512有1条 留时间最大的一条,如果时间相同留id大的,id唯一   
32 55# n512 201009

Answer 1

一下代码供参考with cte as(
select *
,count(stuaddress+sturoom) over(partition by stucode,stuaddress+sturoom) as cnt
,max(stutime) over(partition by stucode,stuaddress+sturoom) as mx_time
from #test) 就报错了  什么原因?create table #test(id int,stucode int,stuaddress varchar(10),sturoom varchar(10),stutime char(6))insert #test select 1,22,'35#','n502','201005'
insert #test select 2,22,'35#','n502','201007'
insert #test select 3,12,'45#','n501','201005'
insert #test select 4,22,'35#','n502','201005'
insert #test select 5,22,'35#','n502','201005'
insert #test select 6,12,'45#','n501','201009'
insert #test select 7,12,'65#','n401','201010'
insert #test select 8,22,'35#','n502','201005'
insert #test select 9,22,'34#','n502','201005'
insert #test select 10,32,'35#','n512','201005'
insert #test select 11,22,'34#','n502','201007'
insert #test select 12,32,'55#','n512','201009'
insert #test select 13,33,'55#','n512','201009'
with cte as(
select *
,count(stuaddress+sturoom) over(partition by stucode,stuaddress+sturoom) as cnt
,max(stutime) over(partition by stucode,stuaddress+sturoom) as mx_time
from #test)select stucode,stuaddress,sturoom,stutime from cte a where stutime=mx_time
and not exists(select 1 from cte  
where stutime=mx_time  
and(( a.stucode=stucode and a.cnt=cnt and a.stutime<stutime)  
or ( a.stucode=stucode and a.cnt<cnt))
)
stucode stuaddress sturoom stutime
----------- ---------- ---------- -------
12 45# n501 201009
22 35# n502 201007
32 55# n512 201009
33 55# n512 201009

Answer 2

呵呵 ,昨天发过吧，回复时提示没有此帖子SELECT `a`.`stucode` AS `stucode`,`a`.`stuaddress` AS `stuaddress`,`a`.`sturoom` AS `sturoom`,MAX(`b`.`stutime`) AS `MAX(b.stutime)` FROM 
(`newtt` `a` JOIN `tty1` `b` ON(((`a`.`stucode` = `b`.`stucode`) AND (`a`.`stuaddress` = `b`.`stuaddress`) AND (`a`.`sturoom` = `b`.`sturoom`)))) WHERE (NOT(EXISTS(SELECT 1 AS `1` FROM `newtt` WHERE ((`a`.`stucode` = `newtt`.`stucode`) AND (`a`.`gs` < `newtt`.`gs`))))) 
GROUP BY `a`.`stucode`,`a`.`stuaddress`,`a`.`sturoom`
VIEW NEWTT:
DELIMITER $$USE `testa`$$DROP VIEW IF EXISTS `newtt`$$CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `newtt` AS SELECT `a`.`stucode` AS `stucode`,`a`.`stuaddress` AS `stuaddress`,`a`.`sturoom` AS `sturoom`,COUNT(`b`.`id`) AS `gs` FROM (`tty1` `a` LEFT JOIN `tty1` `b` ON((((`a`.`stucode` = `b`.`stucode`) AND (`a`.`stuaddress` = `b`.`stuaddress`) AND (`a`.`sturoom` = `b`.`sturoom`)) OR ((`a`.`stucode` = `b`.`stucode`) AND (`a`.`stuaddress` <> `b`.`stuaddress`) AND (`a`.`sturoom` = `b`.`sturoom`) AND ((0 + `a`.`stutime`) > (0 + `b`.`stutime`))) OR ((`a`.`stucode` = `b`.`stucode`) AND (`a`.`stuaddress` <> `b`.`stuaddress`) AND (`a`.`sturoom` = `b`.`sturoom`) AND ((0 + `a`.`stutime`) = (0 + `b`.`stutime`)) AND (`a`.`id` <= `b`.`id`))))) GROUP BY `a`.`stucode`,`a`.`stuaddress`,`a`.`sturoom`$$DELIMITER ;

Answer 3

得到的gs是什么? stucode | stuaddress | sturoom | gs |
+---------+------------+---------+----+
|      12 | 45#        | n501    |  4 |
|      12 | 65#        | n401    |  1 |
|      22 | 34#        | n502    |  8 |
|      22 | 35#        | n502    | 31 |
|      32 | 35#        | n512    |  1 |
|      32 | 55#        | n512    |  2 |
|      33 | 55#        | n512    |  1 |结果不太正确啊?

Answer 4

mysql> SELECT * FROM TTY1;
+------+---------+------------+---------+---------+
| id   | stucode | stuaddress | sturoom | stutime |
+------+---------+------------+---------+---------+
|    1 |      22 | 35#        | n502    | 201005  |
|    2 |      22 | 35#        | n502    | 201007  |
|    3 |      12 | 45#        | n501    | 201005  |
|    4 |      22 | 35#        | n502    | 201005  |
|    5 |      22 | 35#        | n502    | 201005  |
|    6 |      12 | 45#        | n501    | 201009  |
|    7 |      12 | 65#        | n401    | 201010  |
|    8 |      22 | 35#        | n502    | 201005  |
|    9 |      22 | 34#        | n502    | 201005  |
|   10 |      32 | 35#        | n512    | 201005  |
|   11 |      22 | 34#        | n502    | 201007  |
|   12 |      32 | 55#        | n512    | 201009  |
|   13 |      33 | 55#        | n512    | 201009  |
+------+---------+------------+---------+---------+
13 rows in set (0.03 sec)mysql> SELECT `a`.`stucode` AS `stucode`,`a`.`stuaddress` AS `stuaddress`,`a`.`s
turoom` AS `sturoom`,MAX(`b`.`stutime`) AS `MAX(b.stutime)` FROM
    -> (`newtt` `a` JOIN `tty1` `b` ON(((`a`.`stucode` = `b`.`stucode`) AND (`a`
.`stuaddress` = `b`.`stuaddress`) AND (`a`.`sturoom` = `b`.`sturoom`)))) WHERE (
NOT(EXISTS(SELECT 1 AS `1` FROM `newtt` WHERE ((`a`.`stucode` = `newtt`.`stucode
`) AND (`a`.`gs` < `newtt`.`gs`)))))
    -> GROUP BY `a`.`stucode`,`a`.`stuaddress`,`a`.`sturoom`;
+---------+------------+---------+----------------+
| stucode | stuaddress | sturoom | MAX(b.stutime) |
+---------+------------+---------+----------------+
|      12 | 45#        | n501    | 201009         |
|      22 | 35#        | n502    | 201007         |
|      32 | 55#        | n512    | 201009         |
|      33 | 55#        | n512    | 201009         |
+---------+------------+---------+----------------+
4 rows in set (0.03 sec)