简单的我的SQL查询问题 [英] simple my sql query problem

问题描述

您好，



以下sql语句打印用户ID，文章ID和注释。现在一个用户可以在不同的文章中有笔记。我想列出同一篇文章下的所有笔记，而不是重复它。



例如，



id援助标题说明

1 2 article1测试

1 2文章1测试

1 3文章2别的东西



所以我想用文章展示笔记标题 -



标题

笔记



ie

第1条

测试

测试



第2条

别的东西

Hello,

The below sql statement print user id, article id and notes. Now one user can have notes in different articles. I want to list all the notes under same articles together instead of repeating it.

For instance,

uid aid title notes
1 2 article1 test
1 2 article1 testing
1 3 article2 something else

So I want to display notes with article title -

Title
notes

i.e.
article 1
test
testing

article 2
something else

SELECT uid, aid, note
FROM notes_manager
WHERE uid = '123456'
ORDER BY dtime DESC

那里必须是一个简单的解决方案，此刻不要打扰我。

谢谢

There must be an easy solution to this, just not striking me at the moment.
Thanks

推荐答案

查看示例脚本.. 。

Hi,

Check the sample script...

-- User Define Function
CREATE FUNCTION [dbo].[fnSplitString](
    @Input VARCHAR(8000)					-- List of delimited items
  , @Delimiter VARCHAR(8000) = ','				-- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))

BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@Delimiter,@Input,0) <> 0
BEGIN
	SELECT @sItem=RTRIM(LTRIM(SUBSTRING(@Input,1,CHARINDEX(@Delimiter,@Input,0)-1))),
	@Input=RTRIM(LTRIM(SUBSTRING(@Input,CHARINDEX(@Delimiter,@Input,0)+LEN(@Delimiter),LEN(@Input))))

	IF LEN(@sItem) > 0
		INSERT INTO @List SELECT @sItem
	END

	IF LEN(@Input) > 0
		INSERT INTO @List SELECT @Input -- Put the last item in
	RETURN
END

-- Sample Query
DECLARE @articleDtls TABLE(uid INT, aid INT, title VARCHAR(30), notes VARCHAR(100))
DECLARE @strArticle VARCHAR(MAX)

INSERT INTO @articleDtls (uid, aid, title, notes)
VALUES(1, 2, 'article1', 'test'),
(1, 2, 'article1', 'testing'),
(1, 3, 'article2', 'something')
SELECT uid, aid, title, notes FROM @articleDtls 


SELECT @strArticle=STUFF((SELECT  ','+A.title AS [text()]
FROM(SELECT TOP 100 PERCENT T.aid, T.title+(SELECT ','+ notes AS [text()] FROM @articleDtls WHERE aid=T.aid FOR XML PATH('')) 'title' 
	 FROM (SELECT DISTINCT aid, title FROM @articleDtls) T
	 ORDER BY T.aid) A
FOR XML PATH('')),1,1,'') 

SELECT Item FROM dbo.fnSplitString(@strArticle,',')

问候，

GVPrabu

Regards,
GVPrabu

您好，



以下SQL是否有助于

Hello,

Does below SQL helps

SELECT uid, aid, notes FROM notes_manager ORDER BY uid, aid, dtime DESC

它基本上会选择文章明智的注释，其中最新的注释首先出现在所有用户中。如果你想为特定用户这样做，那么添加 WHERE 子句。



问候，

It will basically select article wise notes with latest note appearing first for all users. If you want to do this for a particular user then add WHERE clause.

Regards,

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