急！两个问题，求大神

[bulcor]

数据库地址：http://115.com/file/bejzce1g#RDCsetup.sql

1. 编写一个脚本，可以列出每个成员、已付费用、未付费用。将championship和social两类会员分别合计，并给出总和（划分2大类，是否有注册编号，若有，是CM；若没有，是SM。CM,SM分别统计，再给出总和所有的）
championship member和social member的区别是CM有Registrationnum
feespaid是已经付的费用，CM要交185元，SM要交55元
我写的代码是：
SELECT givenname, familyname, feespaid, 185-feespaid as "Fees", sum(feespaid), sum(185-feespaid)
FROM MEMBER
WHERE Registrationnum IS NOT NULL;
GROUP BY givenname, familyname;

总报错

2. FIXTURES表格中列有去年比赛的列表（俱乐部与俱乐部之间有比赛）。核实查证这些fixtures已被正确完成，并编写一个脚本来确认可能存在的问题。你故意去破坏fixtures表格，即数据改错几个地方，来检测这个脚本是否能查到错误。
（一个星期，一个球队比赛一次，比赛6伦，一轮有4个比赛。）
我的算法是：
- 四个球队，每个球队每个礼拜都有比赛（若此条成立，则每支球队必然一共打了6场比赛）
- 3个客场3个主场
所以结果输出两个表： 1. 计算每周比赛球队数量 2. 计算每个球队的主客场数量
WEEK HOME AWAY
---------- ---- ----
1 GG HH
1 RR SS
2 HH RR
2 SS GG
3 HH SS
3 RR GG
4 HH GG
4 SS RR
5 RR HH
5 GG SS
6 GG RR
6 SS HH


第一个表代码是：
SELECT Weeks, Team,
CASE
WHEN Team=4
THEN 'True'
ELSE 'False'
END "Check"
FROM
(SELECT x as Weeks, y as Team
from
(select week as x, COUNT(DISTINCT home)+COUNT(DISTINCT away) as y
FROM fixture
WHERE home<>away
)
GROUP BY x
ORDER BY x);

又报错 说not a single-group group function 崩溃啊

[大明湖畔_帅锅]

具体内容没怎么看 分组计算要将单个字段都列出来 才能计算其他函数 2个类型差不多

--1
SELECT givenname, familyname, feespaid, 185-feespaid as "Fees", sum(feespaid), sum(185-feespaid)
FROM MEMBER
WHERE Registrationnum IS NOT NULL;
GROUP BY givenname, familyname,feespaid;

--2
select week as Weeks, COUNT(DISTINCT home)+COUNT(DISTINCT away)as Team  
FROM fixture
WHERE home<>away
GROUP BY week 
ORDER BY week

[五更琉璃]

1 你是不是多些了个分号
2 x分组后，不能直接查询y字段了

[bulcor]

第一题还是报错： unknown command begin group by...
第二题可以，我后来用改了一下也可以
代码是这样的：
SELECT Week, Teamnum,
CASE
WHEN Teamnum=4
THEN 'True'
ELSE 'False'
END "Check"
FROM
(SELECT week, COUNT(DISTINCT home)+COUNT(DISTINCT away) as

Teamnum
FROM fixture
WHERE home<>away
GROUP BY week
ORDER BY week);


没有你的简洁，谢谢

[bulcor]

可是你把我的验证语句去掉了啊

[大明湖畔_帅锅]

185-feespaid 这个是默认的赋值吧？ 加引号还报错么

SELECT givenname, familyname, feespaid,'185-feespaid' as Fees, sum(feespaid), sum(185-feespaid)
FROM MEMBER
WHERE Registrationnum IS NOT NULL;
GROUP BY givenname, familyname,feespaid;

[大明湖畔_帅锅]

[Quote=引用 4 楼 的回复:]

可是你把我的验证语句去掉了啊
[/Quote]

只是改了下from里面的语句 上面没问题

[bulcor]

加了以后报错：not a sing-group group function不知道为什么

[bulcor]

[Quote=引用 8 楼 的回复:]

SELECT givenname, familyname, feespaid, 185-feespaid as "Fees", sum(feespaid)over(partition by familyname), sum(185-feespaid)over(partition by familyname)
FROM MEMBER
WHERE Registrationnum IS NOT N……
[/Quote]



恩， 第一题后来我用NVL2函数做出来了
第二题我的算法要用到两个表，这里只列了检测每星期有一场比赛的那个表。
不过这个代码写的真好，我是用DISTINCT解决的有且只有一场的问题的，学习了

[zlb_chen]

1.
SELECT givenname,
       familyname,
       feespaid,
       185 - feespaid as "Fees",
       sum(feespaid),
       sum(185 - feespaid)
  FROM MEMBER
 WHERE Registrationnum IS NOT NULL;
GROUP BY givenname, familyname,feespaid;

2.
SELECT Weeks,
       Team,
       CASE
         WHEN Team = 4 THEN
          'True'
         ELSE
          'False'
       END "Check"
  FROM (SELECT x as Weeks, y as Team
          from (select week as x,
                       COUNT(DISTINCT home) + COUNT(DISTINCT away) as y
                  FROM fixture
                 WHERE home <> away
                 group by week)
         GROUP BY x,y
         ORDER BY x);

自已试下。。。

[小灰狼W]

SELECT givenname, familyname, feespaid, 185-feespaid as "Fees", sum(feespaid)over(partition by familyname), sum(185-feespaid)over(partition by familyname)
FROM MEMBER
WHERE Registrationnum IS NOT NULL;
可能意图还是理解错误，看着改吧


第二道题，实际上并没有要求主场和客场的场次必须对等吧？
按照题目说的，只要每个星期，每个球队有且只有一场比赛，就ok了
with t1 as (select distinct home team from fixture),
t2 as (select distinct week from fixture),
t3 as (select week,home team from fixture union all select week,away from fixture)
select team,max(cnt) as maxcnt,min(cnt) as mincnt from(
select t.team,t.week,count(t3.team) cnt
from (select * from t1,t2) t,t3
where t.team=t3.team(+)
and t.week=t3.week(+)
group by t.team,t.week
)
group by team

[小灰狼W]

查询出来的数值全部为1，说明安排没问题