如果没有找到数据,Sql server查询将连续返回“无数据” [英] Sql server query return “no data” in a row if no data found

查看:302
本文介绍了如果没有找到数据,Sql server查询将连续返回“无数据”的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我的代码能够检索与条形码匹配的书籍代码数据,但在没有数据匹配时则无法检索。如果没有找到数据,我希望它在单元格中打印Bookcode not found。



我尝试了什么:



我在sql server中尝试过一个一个条码。如果我只放置一个没有数据的条形码示例'123',那么它设法显示'找不到书签'但如果我放了3个其他有数据的条形码,则不会包含'未找到书签'。非常感谢你的帮助。



My code able to retrieve book code data that match with the barcode but not when there is no data match. I want it to print "Bookcode not found" in the cell also if there's no data found.

What I have tried:

I tried with one-by-one barcode in sql server. If I put only one barcode example '123' that do not have data then it managed to display 'Bookcode not found' but if I put 3 other barcodes that have data then the 'Bookcode not found' will not be included. I really appreciate your help.

WITH cte AS
(SELECT *,ROW_NUMBER() OVER(PARTITION BY Barcode ORDER BY InvtID Asc) rid 
FROM [Danny].[dbo].[InventoryCustomer] WHERE Barcode In ('ean','9789830093819'))
SELECT InvtID, BOOKCODE = coalesce(InvtID, 'Bookcode not found') 
FROM cte WHERE rid=1 UNION SELECT InvtID = '', BOOKCODE = 'Bookcode not found' 
WHERE NOT EXISTS(SELECT 1 FROM CTE) 

推荐答案

Maciej解决方案可以工作,但是...这不是一个真正的数据检索功能,它是一个演示功能,在你的工作中这样做会更好,效率更高演示语言。



这很简单:检索行,计算它们,并采取适当的措施。在SQL中执行它会返回可变数量的列,这对于演示来说很麻烦。



Maciej的解决方案将起作用,但这意味着有效地运行查询两次,这两者都是如果需要修改查询,则效率低下并存在风险问题。
Maciej solution will work, but ... this isn't really a data retrieval function, it's a presentation function and it would be both much better and more efficient to do this in your presentation language.

It's simple there: retrieve the rows, count them, and take the appropriate action. Doing it in SQL returns a variable number of columns, which is messy for presentation.

Maciej's solution will work, but it means effectively running the query twice, which is both inefficient and risks problems if the query needs to be modified.


简而言之:您可以使用 IF [ ^ ] + EXISTS [ ^ ]:



In a short: you can use IF[^] + EXISTS[^]:

IF NOT EXISTS
(
    SELECT TOP 1 Barcode FROM [Danny].[dbo].[InventoryCustomer] WHERE Barcode In ('ean','9789830093819'))
) 
   SELECT 'Bookcode not found'
ELSE
    SELECT Your_Complex_Query_Here
END


--Actually Table records is filtered by values but you including filtered values as records.This solution is given based on output,i hope this helpful 

CREATE TABLE #InventoryCustomer(InvtID Varchar(20),Bookcode Varchar(20),BarCode Varchar(50)) 

INSERT INTO #InventoryCustomer
Values
      ('CB0212','CB0212','ean'),
      ('DD2921','DD2921','9789830093819');

--In 2008 server need to create split_function for splitting  values.

select 
  ISNULL(InvtID,'') AS InvtID
 ,ISNULL(Bookcode,'Bookcode not found') AS Bookcode 

   FROM split_fun('1,ean,9789830093819',',') AS Stv LEFT JOIN #InventoryCustomer cte   
       ON( Stv.value=Cte.Barcode)

OUTPUT:
-------------------
InvtID | Bookcode
--------------------
	Bookcode not found
CB0212	CB0212
DD2921	DD2921

这篇关于如果没有找到数据,Sql server查询将连续返回“无数据”的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

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