使用循环从sql表中获取 [英] Fetch from sql tables using loop

问题描述

如果我有两张桌子，请说 subject_lesson 和课程

现在，subject_lesson表中的lessonID存储以逗号分隔的lessonID ..如图所示下面..

< td> lessonID

课程表
lessoname
1	一些课程名称
2	另一课程名称
3	另一课

等......



和 subject_lesson 表

我从表单中存储了如下值：

subject_lesson
studylessonID	studyID	lessonIDs
1	3	1,3，

studylessonID （bigint）primary_key auto_increment 1

studyID（ bigint）foreign_key --------->（第三个名为主题，其中studyID是主要的）

lessonIDs（varchar（100））



lessonID （bigint）primary_key auto_increment 1

lessoname（varchar（100）

其中'3'是对应于主题表中主题名称的studyID ..



简而言之，gridview包含3列作为[Course] [Subject] [Lessons] ..现在这些[course]和[subject]可以从 subject_lesson 表中使用studyID和INNER JOIN到主题表。但是第三列的数据库中的值为1,3，（subject_lesson中的lessonID）....而不是 1,3，我想将课程名称显示为一些课程名字，另一课，....使用课程表...我不确定我是否说清楚了！ ：（

解决方案

测试它：

  DECLARE   @ lessons   TABLE （LessonID  INT   IDENTITY （ 1 ， 1 ）， LessonName  VARCHAR （ 30 ））
 
  INSERT   INTO   @ lessons （LessonName）
  SELECT  ' 课程A' 
  UNION   ALL   SELECT  ' 课程B' 
  UNION   ALL   SELECT  ' 课程C' 
  UNION   ALL   SELECT  ' 课程D' 
   -    UNION ALL SELECT'课程E' 
 
 
  DECLARE   @ subject_lesson   TABLE （slid  INT   IDENTITY （ 1 ， 1 ），lids  VARCHAR （ 30 ））
 
  INSERT   INTO   @ subject_lesson （盖子）
  SELECT  '  1,2,3' 
  UNION   ALL   SELECT  '  2,3,4' 
  UNION   ALL   SELECT  '  1,3,5' 
 
;  WITH 课程 AS  
（
  SELECT  slid， CONVERT （< span class =code-keyword> INT ， LEFT （lids，CHARINDEX（' ，'，lids）-1）） AS  lid，右（盖子，LEN（盖子） -  CHARINDEX（' ，'，lids）） AS 剩余
  FROM   @ subject_lesson  
  WHERE  CHARINDEX（' ，'，lids）> 0 
  UNION   ALL  
  SELECT  slid， CONVERT （ INT ， LEFT （剩余，CHARINDEX（< span class =code-string>' ，'，Remainder）-1）） AS  lid， RIGHT （剩余，LEN（剩余） -  CHARINDEX（' ，'，剩余）） AS 剩余
  F ROM 课程
  WHERE  CHARINDEX（' ，'，剩余）> 0 
  UNION  所有 
  SELECT  slid， CONVERT （ INT  ，剩余） AS 剩余， NULL   AS 剩余
  FROM 课程
  WHERE  CHARINDEX（' ，'，Remainder）= 0 
）
  SELECT  L.slid，L.lid，M.LessonName 
  FROM 课程 AS  L < span class =code-keyword> LEFT   JOIN   @ l essons   AS  M  ON  L.lid = M.LessonID 
  ORDER   BY  L.slid，L.lid 

结果：

滑动ID LessonName 
 1 1课A 
 1 2课B 
 1 3课程C 
 2 2课B 
 2 3课C 
 2 4课D 
 3课A 
 3 3课C 
 3 5 NULL 

If I have two tables, say subject_lesson and lesson
Now, lessonIDs in subject_lesson table stores lessonID seperated by comma.. as shown below..

lesson table
lessonID	lessoname
1	Some lesson name
2	another lesson name
3	another lesson

and so on...

and in subject_lesson table
I have stored values from a form as follows:

subject_lesson
studylessonID	studyID	lessonIDs
1	3	1,3,

studylessonID (bigint) primary_key auto_increment 1
studyID (bigint) foreign_key --------->(A third table named subject where studyID is primary)
lessonIDs (varchar(100))

lessonID (bigint) primary_key auto_increment 1
lessoname (varchar(100)
where '3' is a studyID corresponding to a subject name from subject table..

In short, the gridview contains 3 columns as [Course] [Subject] [Lessons] .. Now these [course] and [subject] are simple fetch from subject_lesson table using the studyID and INNER JOIN to subject table. but the third column has values in database as 1,3,(lessonIDs in subject_lesson) .... instead of this 1,3, I want to display the lesson names as "Some lesson name, another lesson,"....using the lesson table.. I'm not sure if I made it clear! :(

解决方案

Test it:

DECLARE @lessons TABLE(LessonID INT IDENTITY(1,1), LessonName VARCHAR(30))

INSERT INTO @lessons (LessonName)
SELECT 'lesson A'
UNION ALL SELECT 'lesson B'
UNION ALL SELECT 'lesson C'
UNION ALL SELECT 'lesson D'
--UNION ALL SELECT 'lesson E'


DECLARE @subject_lesson TABLE (slid INT IDENTITY(1,1), lids VARCHAR(30))

INSERT INTO @subject_lesson (lids)
SELECT '1,2,3'
UNION ALL SELECT '2,3,4'
UNION ALL SELECT '1,3,5'

;WITH Lessons AS
(
	SELECT slid, CONVERT(INT, LEFT(lids, CHARINDEX(',', lids)-1)) AS lid, RIGHT(lids, LEN(lids) - CHARINDEX(',', lids)) AS Remainder
	FROM @subject_lesson
	WHERE CHARINDEX(',', lids)>0
	UNION ALL 
	SELECT slid, CONVERT(INT, LEFT(Remainder, CHARINDEX(',', Remainder)-1)) AS lid, RIGHT(Remainder, LEN(Remainder) - CHARINDEX(',', Remainder)) AS Remainder
	FROM Lessons
	WHERE CHARINDEX(',', Remainder)>0
	UNION ALL
	SELECT slid, CONVERT(INT, Remainder) AS Remainder, NULL AS Remainder
	FROM Lessons
	WHERE CHARINDEX(',', Remainder)=0
)
SELECT L.slid, L.lid, M.LessonName
FROM Lessons AS L LEFT JOIN @lessons AS M ON L.lid = M.LessonID
ORDER BY L.slid, L.lid

Result:

slid   id  LessonName
1      1   lesson A
1      2   lesson B
1      3   lesson C
2      2   lesson B
2      3   lesson C
2      4   lesson D
3      1   lesson A
3      3   lesson C
3      5   NULL

这篇关于使用循环从sql表中获取的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！