用sql查询....... [英] using sql query.......

问题描述

代码是

the code is

CREATE PROCEDURE [dbo].[spFetchAlbumList]
(
@AlbumName  NVARCHAR(50)=null,
@UserGUID   UNIQUEIDENTIFIER=null,
@AlbumGUID  UNIQUEIDENTIFIER=null
)
AS
BEGIN
    SET NOCOUNT ON;
   
     
  SELECT   UserGUID,AlbumGUID,(AlbumImage),AccordinStatus,CreatedDate FROM tblAlbum 
  WHERE    AlbumName=@AlbumName AND UserGUID=@UserGUID AND AlbumGUID=@AlbumGUID  


UNION ALL

SELECT  UserGUID,AlbumGUID,(AlbumImage),AccordinStatus,CreatedDate FROM tblAlbum 
  WHERE AlbumName=@AlbumName AND UserGUID=@UserGUID  AND   AlbumGUID!=@AlbumGUID  


    END

i得到答案：

i got the answer :

AlbumImage	                       AccordinStatus
cartoon.png.png	                             6
cartoon.png	                             1
_hccABq.png	                             2
Chrysanthemum.jpg.png	                     3
Desert.jpg.png	                             5
Koala.jpg.png	                             8

但我需要以下类型的答案：

But I need the answer following type:

AlbumImage	                AccordinStatus
cartoon.png.png	                      6
Koala.jpg.png                         8
Desert.jpg.png                        5
Chrysanthemum.jpg.png                 3
_hccABq.png                           2
cartoon.png                           1

[edit]已添加代码块 - OriginalGriff [/ edit]

[edit]Code block added - OriginalGriff[/edit]

推荐答案

As 原稿inalGriff [ ^ ]在问题的评论中提到，你必须使用 ORDER BY子句 [ ^ ]。



注意，您应该使用更有效的查询，例如：

As OriginalGriff[^] mentioned in the comment to the question, you have to use ORDER BY clause[^].

Note, you should use more efficient query, such as:

SELECT   UserGUID, AlbumGUID, (AlbumImage), AccordinStatus, CreatedDate
FROM tblAlbum
WHERE AlbumName=@AlbumName AND UserGUID=@UserGUID -- AND (AlbumGUID=@AlbumGUID OR AlbumGUID!=@AlbumGUID)
ORDER BY AccordinStatus DESC

想一想！

Think of it!

根据OP的评论...



您正在寻找与分页<类似的内容/ a> [ ^ ]。

另见这些文章：

通过查询结果进行分页 [ ^ ]

分页SQL Server结果集 [ ^ ]



我建议使用Linq来交流提升您正在寻找的功能。看看下面的代码：

According to OP's comments...

You're looking for something similar to pagination[^].
See also these articles:
Paging Through a Query Result[^]
Paging SQL Server result sets[^]

I'd suggest to use Linq to achieve functionality you're looking for. Have a look at below code:

void Main()
{
	//create DataTable object
	DataTable dt = CreateMyDataTable();
	//display 1. image
	DisplayNextImage(dt, 0);
	//display 3. image
	DisplayNextImage(dt, 2);
}

// Define other methods and classes here
public DataTable CreateMyDataTable()
{
	DataTable dt = new DataTable();
	DataColumn dc  = new DataColumn("AlbumImage", Type.GetType("System.String"));
	dt.Columns.Add(dc);
	dc  = new DataColumn("AccordinStatus", Type.GetType("System.Int32"));
	dt.Columns.Add(dc);
	dt.Rows.Add(new Object[]{"cartoon.png.png", 6});
	dt.Rows.Add(new Object[]{"cartoon.png", 1});
	dt.Rows.Add(new Object[]{"_hccABq.png", 2});
	dt.Rows.Add(new Object[]{"Chrysanthemum.jpg.png", 3});
	dt.Rows.Add(new Object[]{"Desert.jpg.png", 5});
	dt.Rows.Add(new Object[]{"Koala.jpg.png", 8});
	return dt;
}

//display image
public void DisplayNextImage(DataTable dt, int current)
{
	var qry = dt.AsEnumerable().Skip(current).Take(1);
	foreach(var row in qry)
	{
		Console.WriteLine("{0}\t\t{1} ", row.Field<string>("AlbumImage"), row.Field<int>("AccordinStatus"));
	}
}</int></string>

结果：

Result:

cartoon.png.png    6
_hccABq.png    2

如需了解更多信息，请参阅：使用LINQ 页面大结果集[ ^ ]

这篇关于用sql查询.......的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！