带CASE的SQL语句联接 [英] SQL Statement Join With CASE

问题描述

我对MS ACCESS的下面这3个表有疑问，无法执行"SQL CASE IF ELSE"操作.

I have problem with this 3 table below from MS ACCESS to do "SQL CASE IF ELSE" where I don't how to start.

表A(注册)

Name    | Desc      | Amount    | Year
NameA   | JAN&NOV   | 100.00    | 2015
NameA   | BOOKS     | 70.00     | 2015
NameA   | UNIFORM   | 100.00    | 2015
NameB   | JAN&NOV   | 100.00    | 2015

表B(每月付款及其他)

Table B (Montly Payment and Others)

Name    | Desc      | Amount    | Year
NameA   | PAY FEB   | 100.00    | 2015
NameB   | PAY MAC   | 100.00    | 2015
NameA   | PAY MAC   | 100.00    | 2015
NameB   | OTHERS    | 20.00     | 2015
NameB   | PAY APR   | 100.00    | 2015

表C(学生列表)

Name    | Year      | Class 
NameA   | 2015      | A 
NameB   | 2015      | B

结果:

Name    | Year  | Class | Jan&Nov   | Pay Feb   | Pay Mac   | Pay Apr
NameA   | 2015  | A     | 100.00    | 100.00    | 100.00    | 000.00
NameB   | 2015  | B     | 100.00    | 100.00    | 100.00    | 100.00

我需要使用此3表的数据创建字段Pay Jan&Nov，Pay Feb到PayOct.如果数据不存在，我需要将其格式设置为"00.00"或"NOT PAID".

I need to create field Pay Jan&Nov, Pay Feb till Pay Oct with data based of this 3 table. If data do not exist I need to format it as "00.00" or "NOT PAID".

我希望任何有解决方案的人都可以帮助我.我实际上是SQL语句的新手.

I hope anybody who have a solutions please help me. I am new of SQL statement actually.

推荐答案

感谢fthiella，DeadZone，Beth和Thorsten Kettner给了我正确的指导.由于尝试了许多天直接在MS Access IDE上直接使用您的解决方案，所以终于找到了一个窍门.非常感谢您解决我的问题.诀窍是:-

Thanks you to fthiella,DeadZone,Beth and Thorsten Kettner who give me a right direction. Since trying many day using yours solution directly on MS Access IDE finally I found a trick. I need to thanks you very much to solving my problem. The trick is :-

A)如果通过选择记录为空或已存在，然后使用(选择Nz(MAX(m.amount)，'NOT PAID')作为PAYFEB.

A) IF RECORD IS EMPTY or EXIST by SELECTION then using (Select Nz(MAX(m.amount),'NOT PAID') as PAYFEB.

诸如LEFT JOIN或Direct SUBQUERIES之类的所有解决方案实际上都在起作用.但是我更喜欢SUBQUERIES，因为它具有更多的组织性，而且我可以查看是否有任何语法或字段未注册.

All kind solutions like LEFT JOIN or Direct SUBQUERIES are working actually. But I more prefer SUBQUERIES because more organize and I can see if any syntax or field are not register.

最后，如果没有您的支持，我将无法获得此问题的答案.非常感谢你们.

Lastly, without yours support I cannot get this answer of this matter. Thanks you very much guys.

这篇关于带CASE的SQL语句联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！