如何编写一个返回从当前SELECT返回的行的SQL SELECT [英] How to write an SQL SELECT that returns rows that do not get returned from my current SELECT

查看:86
本文介绍了如何编写一个返回从当前SELECT返回的行的SQL SELECT的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

 string query =select ad.student_id,ad.full_name from add_student ad inner join billing b on ad.student_id = b.student_id where month(b.fee_of_month)= month(#+ DateTime .Now.ToShortDateString()+#)和year(b.fee_of_month)= year(#+ DateTime.Now.ToShortDateString()+#); 





我使用上面的查询来检索那些已经支付了本月费用的学生的记录。此查询工作正常。



我想要一个新的查询来检索没有支付费用的学生的行



如果我使用如下的查询,那么它将不会按预期返回行。请建议我可以用来完成任务的查询。谢谢。



 string query =select ad.student_id,ad.full_name from add_student ad inner join billing b on ad.student_id = b .student_id月份(b.fee_of_month)<>月份(#+ DateTime.Now.ToShortDateString()+#)和年份(b.fee_of_month)=年份(#+ DateTime.Now.ToShortDateString()+ #); 

解决方案

您的新查询将如下所示。抱歉,我没有时间构建测试用例来为您测试。这个概念是你从那些已经付费的student_ids和从add_student开始的那个student_ids中选择那些不在已经付费的那些中的student_ids。我希望这会对你有所帮助。



 string query =select ad.student_id,ad.full_name来自add_student ad,其中ad.student_id不在(从结算b中选择b.student_id,其中月份(b.fee_of_month)=月份(#+ DateTime.Now.ToShortDateString()+#)和年份(b.fee_of_month)=年份(#+ DateTime.Now.ToShortDateString( )+#)); 


使用

  SELECT  *  FROM  add_student ad  inner   join 结算b   ad.student_id = b.student_id  HAVING  select  ad.student_id,ad.full_name 来自 add_student ad  inner  加入结算b   ad.student_id = b.student_id 其中​​ month(b.fee_of_month)= month(#  + DateTime.Now.ToShortDateString()+#)年(b.fee_of_month)=年(#  + DateTime.Now.ToShortDateString()+#))


string query = "select ad.student_id,ad.full_name from add_student ad inner join billing b on ad.student_id = b.student_id where month (b.fee_of_month) = month(#"+DateTime.Now.ToShortDateString()+"#) and year(b.fee_of_month)= year(#"+DateTime.Now.ToShortDateString()+"#)";



I am using above query to retrieve record of those students that have paid fees for this month. This query is working fine.

I want a new query to retrieve rows for those students who have not paid their fees.

If I use query like below, then it will not return rows as expected. Please suggest a query that I can use to achieve my task. Thank you.

string query = "select ad.student_id,ad.full_name from add_student ad inner join billing b on ad.student_id = b.student_id where month (b.fee_of_month) <> month(#"+DateTime.Now.ToShortDateString()+"#) and year(b.fee_of_month)= year(#"+DateTime.Now.ToShortDateString()+"#)";

解决方案

Your new query will look something like this. Sorry, I do not have time to build a test case to test it for you. The concept is that you select from billing those student_ids that have paid and from add_student you select those student_ids that are not in the set of those that have paid. I hope this helps you.

string query = "select ad.student_id,ad.full_name from add_student ad where ad.student_id not in (select b.student_id from billing b where month (b.fee_of_month) = month(#"+DateTime.Now.ToShortDateString()+"#) and year(b.fee_of_month)= year(#"+DateTime.Now.ToShortDateString()+"#))";


use

SELECT * FROM add_student ad inner join billing b on ad.student_id = b.student_id HAVING  (select ad.student_id,ad.full_name from add_student ad inner join billing b on ad.student_id = b.student_id where month (b.fee_of_month) = month(#"+DateTime.Now.ToShortDateString()+"#) and year(b.fee_of_month)= year(#"+DateTime.Now.ToShortDateString()+"#))


这篇关于如何编写一个返回从当前SELECT返回的行的SQL SELECT的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆