sql语句请教。

发布网友发布时间：2022-04-10 12:48

共3个回答

热心网友时间：2022-04-10 14:17

修改一下：

select a.id,a.name from a , (
select replace(b.aid||b.bid,'8888','') ids ,b.yi from b where b.aid=8888 or b.bid=8888
)b
WHERE a.id=b.ids
GROUP BY a.id , a.name
ORDER BY MIN(b.yi)

测试：

idle> WITH a (ID,NAME) AS (
  2  SELECT 6666,'用户1' FROM al
  3  UNION ALL SELECT 7777,'用户2' FROM al
  4  UNION ALL SELECT 8888,'用户3' FROM al
  5  UNION ALL SELECT 9999,'用户4' FROM al
  6  ),
  7  b (id,aid,bid,yi) AS (
  8  SELECT 1,8888,9999,0 FROM al
  9  UNION ALL  SELECT 2,8888,9999,1 FROM al
10  UNION ALL  SELECT 3,9999,8888,1 FROM al
11  UNION ALL  SELECT 4,7777,8888,1 FROM al
12  UNION ALL  SELECT 5,8888,6666,1 FROM al
13  )
14  select a.id,a.name from a , (
15  select replace(b.aid||b.bid,'8888','') ids ,b.yi  from b where b.aid=8888 or b.bid=8888
16  )b
17  WHERE a.id=b.ids
18  GROUP BY a.id , a.name
19  ORDER BY MIN(b.yi);

        ID NAME
---------- -----
      9999 用户4
      6666 用户1
      7777 用户2

热心网友时间：2022-04-10 15:35

declare @t table (
x int,y int,z datetime
)
insert into @t
select case when B.bid='8888' then B.aid when B.aid='8888' then B.bid end,
yi,time
from B order by yi,time desc
select * from @t a where z=(select top 1 z from @t where x=a.x)

yi和time排序都有了。
为了让你看到时间排序
最后一句select * from @t a where z=(select top 1 z from @t where x=a.x)
我选了所有。不需要的话可以这样
select x,y from @t a where z=(select top 1 z from @t where x=a.x)

热心网友时间：2022-04-10 17:10

WITH a (ID,[NAME]) AS (
   SELECT 6666 as id,'用户1' as [NAME]
   UNION ALL SELECT 7777 id,'用户2' [NAME]
   UNION ALL SELECT 8888 id,'用户3' [NAME]
   UNION ALL SELECT 9999 id,'用户4' [NAME]
    ),
    b (id,aid,bid,yi) AS (
   SELECT 1,8888,9999,0
   UNION ALL  SELECT 2,8888,9999,1
   UNION ALL  SELECT 3,9999,8888,1
   UNION ALL  SELECT 4,7777,8888,1
   UNION ALL  SELECT 5,8888,6666,1
  )
select a.ID,a.Name from
(select  distinct aid  from b where bid='8888'
union
select distinct bid  as aid from b where aid='8888') as b1 inner join a on b1.aid=a.id
或者
select a.id,a.[name] from
(
select distinct bid=(case bid when '8888' then aid else bid end)
from (select aid,bid from b where aid='8888' or bid='8888') as k
) m inner join a on m.bid=a.id

追问select a.ID,a.Name from
(select distinct aid from b where bid='8888'
union
select distinct bid as aid from b where aid='8888') as b1 inner join a on b1.aid=a.id

这句可以达到效果，但是怎么排序呢，