---
title: 'MYSQL'
disqus: hackmd
---

MYSQL
===

MySQL
BCS_INVOICE
INVOICE_ID IN ('0c302d23-5b44-4caa-a604-ab2e9b4ea5d6','4ac0654e-527f-455f-b1f5-8e3061618730','669f52c2-27f5-4594-b3bf-f657d4aa8d5f','71fd8c4a-2478-4473-b49e-0fb43978dbd4')

CAMPAIGN_ID IN ('7f4e7861-65ce-45bf-b356-7fe43c4d8e9b','d0d017c7-0da1-46ee-9b4f-3541c3754134','41bbfae5-792e-4f1a-8e83-bc5b9431f12c','34576dee-7df6-4b2f-a733-bc65893bc522')

------------------------------------------------------------------------------------------------------------------------------------------------

改寫 拿出的欄位 及測試 MySQL 於 db.repository 內

@Query(value = "SELECT PC.PRIZE_CODE,PL.MID ,PL.MODIFY_TIME FROM BCS_PRIZE_CODE PC LEFT JOIN BCS_PRIZE_LIST PL ON PC.PRIZE_LIST_ID = PL.PRIZE_LIST_ID WHERE PC.GROUP_ID = :groupId",nativeQuery = true)
public List<Object[]> findPrizeCodesAndPrizeListByGroupId(@Param("groupId")String groupId);

==>SELECT PC.PRIZE_CODE,PL.MID ,PL.MODIFY_TIME FROM BCS_PRIZE_CODE PC LEFT JOIN BCS_PRIZE_LIST PL ON PC.PRIZE_LIST_ID = PL.PRIZE_LIST_ID WHERE PC.GROUP_ID ="";

@Query(value = "SELECT PC.PRIZE_CODE,PL.MID ,PL.MODIFY_TIME FROM BCS_PRIZE_LIST PL LEFT JOIN BCS_PRIZE_CODE PC ON PL.PRIZE_LIST_ID = PC.PRIZE_LIST_ID WHERE PL.PRIZE_ID = ?1 " , nativeQuery = true)
public List<Object[]> findPrizeListAndCodeByPrizeId(String prizeId);

SELECT PC.PRIZE_CODE,PL.MID ,PL.MODIFY_TIME FROM BCS_PRIZE_LIST PL LEFT JOIN BCS_PRIZE_CODE PC ON PL.PRIZE_LIST_ID = PC.PRIZE_LIST_ID WHERE PL.PRIZE_ID ="";

------------------------------------------------------------------------------------------------------------------------------------------------

INSERT INTO BCS_INVOICE (欄位名a,欄位名b,欄位名c) VALUES(欄位值a1,欄位值2,欄位值3),(欄位值a4,欄位值5,欄位值6),(欄位值a7欄位值,8,欄位值9);

INSERT INTO BCS_INVOICE (INVOICE_ID,CAMPAIGN_ID,DRAW_STATUS,STATUS,IS_FINISHED,LOTTERY_ID) 
VALUES
('76e59659-9a66-11ea-b9c4-000d3a509410','db2e3728-6801-48c8-8191-38a7eb7855be',1,'VALID','N','5774cfdd-e918-42d8-827b-365e0f955dbf'),
('b0d80346-9a66-11ea-b9c4-000d3a509410','db2e3728-6801-48c8-8191-38a7eb7855be',1,'VALID','N','5774cfdd-e918-42d8-827b-365e0f955dbf'),	 
('b53a1ffc-9a66-11ea-b9c4-000d3a509410','db2e3728-6801-48c8-8191-38a7eb7855be',1,'VALID','N','5774cfdd-e918-42d8-827b-365e0f955dbf'),
('9222c850-9a66-11ea-b9c4-000d3a509410','db2e3728-6801-48c8-8191-38a7eb7855be',1,'VALID','N','5774cfdd-e918-42d8-827b-365e0f955dbf'),
('9c3868db-9a66-11ea-b9c4-000d3a509410','db2e3728-6801-48c8-8191-38a7eb7855be',1,'VALID','N','5774cfdd-e918-42d8-827b-365e0f955dbf');

----------------------------------------------------------

PrizeListGroup

groupId = 019cec0f-5cc5-4a31-9680-10dd57a608ec

2f25ace2-039c-4b8b-8cc6-9804b005c53e

SQL條件:

CAMPAIGN_ID	=	db2e3728-6801-48c8-8191-38a7eb7855be				name:20200413test02

DRAW_STATUS = 	1
STATUS		=	VALID

DB 非空:

INVOICE_ID 	= 	(每張發票之PK)(拿已經存在的發票INVOICE_ID可嗎?不行 是唯一的 So 假資料不重複
IS_FINISHED =	N


CAMPAIGN_ID	=	3212c453-0e96-4090-898c-857b01643e25
INVOICE_ID select uuid();


其他欄位?=>先不考慮

INVOICE_ID :(8-4-4-4-12)=>Q:如何1.產生3000筆不重複((8-4-4-4-12))且DB不存在的資料? 2.進DB?

lotteryId=c5c90c24-1b4a-4186-8b64-d55b053194b3

------------------------------------------------------------------------------------------------

line_uid IN ('Uad6f0fe4497d4583872e0d102ffb2238')

select qt.name,qt.is_optional,qt.order_num,qt.topic_type,qtd.option_answer, count(qa.questionnaire_topic_id) as qtiCount 
from questionnaire_answer qa 
join questionnaire_topic qt 
on qa.questionnaire_topic_id = qt.id 
left join questionnaire_topic_detail qtd on qa.questionnaire_topic_detail_id = qtd.id 
where qa.questionnaire_main_id = :questionnaireMainId 
group by qt.id, qt.is_optional, qt.name, qt.order_num, qt.topic_type, qtd.option_answer, qa.questionnaire_topic_id 
order by qt.id ;

select qt.name,qt.is_optional,qt.order_num,qt.topic_type,qtd.option_answer, count(qa.questionnaire_topic_id) as qtiCount 
from QUESTIONNAIRE_TOPIC qt 
join QUESTIONNAIRE_TOPIC_DETAIL qtd on qt.id = qtd.questionnaire_topic_id 
left join QUESTIONNAIRE_ANSWER  qa on qtd.id = qa.questionnaire_topic_detail_id 
where qt.questionnaire_main_id = 4
group by  qt.is_optional, qt.name, qt.order_num, qt.topic_type, qtd.option_answer, qa.questionnaire_topic_id , qtd.id
order by qtd.id ;

----------------------------------------------