msSQL 문제

이정민·2022년 3월 29일
0


select EmployeeID id
	  ,concat(len(replace(Notes,' ','')),'(',substring(Notes,1,10),')') info
      ,notes
from Employees;


select EmployeeID id
	  ,LastName
      ,concat(FirstName,'(',charindex('German',Notes),')') German
      ,Notes
from Employees;

select supplierId
	  ,suppliername
      ,contactName
      ,country
      ,phone
from suppliers
where (phone not like '([0-9][0-9][0-9])%') and country = 'Germany'

select supplierid
	  ,suppliername
      ,contactname
      ,country
      ,phone
      ,iif(phone like '([0-9][0-9][0-9])%','Exact','Inexact') notes
from suppliers
where phone like '([0-9]%)%' and country = 'Germany'

select supplierid
	  ,suppliername
      ,contactname
      ,country
      ,case country when 'canada' then 1 when 'france' then 2 else 3 end candidate
from suppliers
where country = 'canada' or country = 'france' or country = 'australia'
order by candidate, contactname


where Country in('canada', 'france', 'australia') 로 대체할 수 있다


select shipperid
	  ,min(orderdate) date1
      ,max(orderdate) date2
      ,count(*) orderCount
      ,count(distinct customerid) customerCount
from orders
group by shipperID


select employeeid, count(*) jumun
from orders
group by employeeid
having count(*) >= 30
order by jumun desc

select top 1 
	   customerID
	  ,count(orderID) orderCount
      ,stuff((select concat(',',orderDate)
      		  from orders
              where customerID = a.customerID
              for xml path('')),1,1,'') notes
from orders a
group by customerID
order by orderCount desc
profile
안녕하세요.

0개의 댓글